data in a single record

Question

Below is the sql select query output.

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

Here Col1, Col2, Col3, Col4, Col5 are repeated.. I just want all the data in a single record (removing NULL) Just like 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 regards

Thanks in anticipation.

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 out.

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