none
How to concatenate the data from different rows into a single row

    Question

  • Hello All,

    I have a SQL Query.   select distinct ps.podsID,

    CASE

    WHEN CO.CountyBoundaryState in ('TO','LO') THEN 'Offshore'

    else 'Onshore'

    end AS 'Onshore/OffShore'

    from

    PIPELINESUMMARY ps

    join

    DERIVEDASSET.DBO.County CO on ps.LineGUID = CO.LINE_GUID

    where

    podsID = 563

    order

    by PODSID                                                                                                                                                            For this PODSID I am getting the 2 records in different rows one is offshore and the other one is onshore. I want to get this in one single record with comma seperated like this  563   onshore,offshore.       Please help me with any suggestions.  Thank You

    Tuesday, July 17, 2012 3:30 PM

Answers

  • You can use the standard means of concatenating rows in SQL, e.g.

    ;with cte AS ( select distinct ps.podsID,
    
    CASE
    
    WHEN CO.CountyBoundaryState in ('TO','LO') THEN 'Offshore'
    else 'Onshore'
    end AS 'Onshore/OffShore'
    from
    
    PIPELINESUMMARY ps
    join
    
    DERIVEDASSET.DBO.County CO on ps.LineGUID = CO.LINE_GUID
    where podsID = 563)
    
    SELECT PodsID, STUFF((SELECT ', ' + 
    [Onshort/Offshore] FROM cte c1
    where c1.PodsID = c.PodsID
    FOR XML PATH('')),1,2,'') AS [Onshore/Offshore]
    FROM cte c
    GROUP by PODSID                                                     
    ORDER BY PODSID

    See these blogs to understand this better

    MSDN thread about concatenating rows
    Making a list and checking it twice
    Concatenating Rows - Part 1
    Concatenating Rows - Part 2

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by SQL Novice 01 Tuesday, July 17, 2012 3:35 PM
    • Marked as answer by srisql Wednesday, July 18, 2012 8:34 PM
    Tuesday, July 17, 2012 3:34 PM

All replies