Grouping and Merging using SQL Query

Posted by Sasikala under Sql Server on 3/12/2014 | Points: 10 | Views : 613 | Status : [Member] | Replies : 1
Hi,

I have the following given table as,

TableName : #TempDetails

ServiceID ServiceName NoofRecords Condition Narration Remarks Code Location BillMonth BillYear ProcessMonth ProcessY ear
1 DM 100 -1 Narrate Remark CNT177 TN 3 2011 3 2014
36 FS 150 -1 Narrate Remark CNT177 TN 3 2011 3 2014
5 Pay 50 -1 Narrate Remark CNT177 TN 3 2011 3 2014
1 DM 30 -1 Narrate Remark CNT222 TN 3 2011 3 2014
5 Pay 150 -1 Narrate Remark CNT222 TN 3 2011 3 2014
33 Sal 20 -1 Narrate Remark CNT222 TN 3 2011 3 2014

I need the following as output

ServiceID ServiceName NoofRecords Condition Narration Remarks Code Location BillMonth BillYear ProcessMonth ProcessYear
1 DM 130 -1 Narrate Remark CNT177 TN 3 2011 3 2014
36 FS 150 -1 Narrate Remark CNT177 TN 3 2011 3 2014
5 Pay 200 -1 Narrate Remark CNT177 TN 3 2011 3 2014
33 Sal 20 -1 Narrate Remark CNT222 TN 3 2011 3 2014
Based on ServiceID the records has to be grouped and the Code column can choose neither one when the Service is in both the code, if the Service exists only in one code then its appropriate code has to be displayed. Remaining column values are same for all the records and has to be reflected in the outpur. Does any one know it

I used this query but I dont have I idea for remaining colums to fetch and also to choose common Code.

select ServiceID, ServiceName, Sum(NoOfRecords) as RecordCount from #TempDetails group by ServiceID, ServiceName




Responses

Posted by: Bandi on: 3/14/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
select ServiceID , ServiceName , Condition , Narration , Remarks , Location ,
BillMonth , BillYear , ProcessMonth , ProcessYear
, Sum(NoOfRecords) as RecordCount , MAX(Code) Code
from #TempDetails
GROUP BY ServiceID , ServiceName ,
Condition , Narration , Remarks , Location ,
BillMonth , BillYear , ProcessMonth , ProcessYear


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sasikala, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response