data in a single record

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

  • How to avoid Sql Query Timeout
  • SQL Server Hash Indexes
  • Using CASE Statement inside IN Clause
  • How do I form a query with a running count retaining the order
  • SQL - Group By with Left Join
  • The sql server size file doesn't decrease when i delete my records
  • 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.

    2 Solutions collect form web for “data in a single record”

    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

    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
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.