CODEWITHSUNDEEP
sqlsql-serverselectgroup-bysql-server-2012
staq
staq

Reputation: 163

Summaries data into one row in SQL server

I have data as shown in the following table which i'm getting after quering different tables in my database. However, i would like to know an efficient way of putting this data into one Row using SQL query.

sample data as below:

ID  Ref_Num Status  Start_Dt    Start_Status    End_Dt  End_Status  Old_Ref Old_Ref_Dt
226173  100443677   Process Completed   02/04/2014  Receive Process Details NULL    NULL    NULL    NULL
226173  100443677   Process Completed   NULL    NULL    14/04/2014  Data Received on Completion NULL    NULL
226173  100443677   Process Completed   NULL    NULL    NULL    NULL    NULL    NULL
226173  100443677   Process Completed   NULL    NULL    NULL    NULL    100443689   15/04/2014

Upvotes: 2

Views: 58

Answers (2)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT A.ID, A.Ref_Num, A.Status, 
         MAX(A.Start_Dt) AS Start_Dt, MAX(A.Start_Status) AS Start_Status, 
         MAX(A.End_Dt) AS End_Dt, MAX(A.End_Status) AS End_Status, 
         MAX(A.Old_Ref) AS Old_Ref, MAX(A.Old_Ref_Dt) AS Old_Ref_Dt
FROM tableA A
GROUP BY A.ID, A.Ref_Num, A.Status

Upvotes: 0

StuartLC
StuartLC

Reputation: 107317

You can (ab)use the fact that nulls are overlooked when using aggregates*, and then apply these aggregates with a GROUP BY:

SELECT ID, Ref_Num, Status, MAX(Start_Dt), MAX(Start_Status), ...
FROM MyTable
WHERE ...
GROUP BY ID, Ref_Num, Status;

From the data, I've assumed that ID Ref_Num Status and Start_Dt are the non-aggregate columns. I've also assumed that of the 'sparse data' that only one row has data, the rest have nulls, otherwise we will face challenges with ordering and would need to be more selective about choice of aggregates for each column.

* except COUNT

Upvotes: 2

Related Questions