none
Bring the columns value in one row in sql

    Question

  • If you see the below example i have values in 4 different rows ..where i need to combine all in row as i do not want to see duplicate values...any suggestions will be very helpful.
    Id PROGRAM_NAME source Support Status Program Denial Reason
    SS00000N   PROGRAM1 NULL NULL NULL
    SS00000N PROGRAM1 NULL In Process NULL
    SS00000N PROGRAM1 Electronic NULL NULL
    SS00000N PROGRAM1 NULL NULL Not Valid

    Much appreciate your response.

    Sunday, February 10, 2019 11:48 PM

Answers

  • Hi yashwan,

    Would like this one ?

     

    ---drop table test 
    create table test 
    (Id	varchar(10),
    [PROGRAM_NAME] varchar(10),
    [source] varchar(10),
    [Support Status] varchar(10),
    [Program Denial Reason]	varchar(10))
    insert into test values 
    ('SS00000N','PROGRAM1',NULL,NULL,NULL),
    ('SS00000N','PROGRAM1',NULL,'In Process',NULL),
    ('SS00000N','PROGRAM1','Electronic',NULL,NULL),
    ('SS00000N','PROGRAM1',NULL,NULL,'Not Valid')
    
    select Id,[PROGRAM_NAME], max([source]) as [source], 
    max([Support Status]) as [Support Status],
    max([Program Denial Reason]) as [Program Denial Reason]
    from test 
    group by  Id,[PROGRAM_NAME]
    /*
    Id         PROGRAM_NAME source     Support Status Program Denial Reason
    ---------- ------------ ---------- -------------- ---------------------
    SS00000N   PROGRAM1     Electronic In Process     Not Valid
    */
    
    


    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by yashwan Monday, February 11, 2019 7:09 PM
    Monday, February 11, 2019 2:25 AM
  • You have different methods

    if you're sure only one non null value will exist for each of column for every Id, Program_name group then easiest wy is this

    SELECT Id,
    PRGRAM_NAME
    MIN(source) AS source,
    MIN([Support Status]) AS [Support Status],
    MIN([Program Denial Reason]) AS [Program Denial Reason]
    FROM TableName
    GROUP BY Id,
    PRGRAM_NAME


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by yashwan Monday, February 11, 2019 7:09 PM
    Monday, February 11, 2019 5:36 AM

All replies

  • Hi yashwan,

    Would like this one ?

     

    ---drop table test 
    create table test 
    (Id	varchar(10),
    [PROGRAM_NAME] varchar(10),
    [source] varchar(10),
    [Support Status] varchar(10),
    [Program Denial Reason]	varchar(10))
    insert into test values 
    ('SS00000N','PROGRAM1',NULL,NULL,NULL),
    ('SS00000N','PROGRAM1',NULL,'In Process',NULL),
    ('SS00000N','PROGRAM1','Electronic',NULL,NULL),
    ('SS00000N','PROGRAM1',NULL,NULL,'Not Valid')
    
    select Id,[PROGRAM_NAME], max([source]) as [source], 
    max([Support Status]) as [Support Status],
    max([Program Denial Reason]) as [Program Denial Reason]
    from test 
    group by  Id,[PROGRAM_NAME]
    /*
    Id         PROGRAM_NAME source     Support Status Program Denial Reason
    ---------- ------------ ---------- -------------- ---------------------
    SS00000N   PROGRAM1     Electronic In Process     Not Valid
    */
    
    


    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by yashwan Monday, February 11, 2019 7:09 PM
    Monday, February 11, 2019 2:25 AM
  • You have different methods

    if you're sure only one non null value will exist for each of column for every Id, Program_name group then easiest wy is this

    SELECT Id,
    PRGRAM_NAME
    MIN(source) AS source,
    MIN([Support Status]) AS [Support Status],
    MIN([Program Denial Reason]) AS [Program Denial Reason]
    FROM TableName
    GROUP BY Id,
    PRGRAM_NAME


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by yashwan Monday, February 11, 2019 7:09 PM
    Monday, February 11, 2019 5:36 AM
  • Thanks .. this works
    Monday, February 11, 2019 7:09 PM
  • thanks ,also works
    Monday, February 11, 2019 7:09 PM