Data in one record

Question

Below is the output of the sql select query.

Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9 ------------------------------------------------------------------------------------- General-Surgery John 193850 21/06/2013 Smith NULL 704.08 NULL NULL General-Surgery John 193850 21/06/2013 Smith 2510 NULL NULL NULL General-Surgery John 193850 21/06/2013 Smith NULL NULL NULL 19950 General-Surgery John 193850 21/06/2013 Smith NULL NULL 0 NULL 

Col1, Col2, Col3, Col4, Col5 are repeated here. I just want all the data in one record (deleting NULL) As below ..

 Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9 --------------------------------------------------------------------------------------- General-Surgery John 193850 21/06/2013 Smith 704.08 2510 19950 0 

Please help me in this regard.

Thanks pending.

Answer 1

 select Col1, Col2, Col3, Col4, Col5, max(isnull(Col6,0)), max(isnull(Col7,0)), max(isnull(Col8,0)), max(isnull(Col9,0)) from table1 group by Col1, Col2, Col3, Col4, Col5 

SQL Fiddle

Answer 2

Hope this helps ypu.

 WITH TempT AS ( --YOUR SELECT QUERY FOR THE FIRST TABLE ) SELECT Col1, Col2, Col3, Col4, Col5, MAX(isnull(Col6,0)), MAX(isnull(Col7,0)), MAX(isnull(Col8,0)), MAX(isnull(Col9,0)) FROM TempT GROUP BY Col1, Col2, Col3, Col4, Col5