Home > SQL Challenge > T-SQL – Concatenate row values in Transact-SQL

T-SQL – Concatenate row values in Transact-SQL

Here is very good blog about how to concatenate row values in Transact-SQL.

I recently needed to create a report for mainframe directors to show all the mainframe assets each director is linked to.

When the number of items is small and known beforehand, the most common approach is to use group by, case statement and max function.

When the number of items is unknown, concatenating row values can be very tricky. Fortunately, TSQL has provided some very elegant ways developers can use. Using Recursive CTE is one way. But my favorite is to use the FOR XML clause with PATH mode, which is available even in SQL 2005.

image

In this example, #temp table contains DirectorID, and DeviceName that each Director is linked to. One Director can be linked to several mainframe assets.

The above query will show one row per DirectorID, with each mainframe asset device name being concatenated together and separated by a comma.

image

About these ads
Categories: SQL Challenge
  1. Sherry Li
    January 7, 2011 at 5:19 pm | #1

    SELECT p1.lPIMFId
    , ( SELECT description + ‘,’
    FROM #temp p2
    WHERE p2.lPIMFId = p1.lPIMFId
    ORDER BY
    description
    FOR XML PATH(”) ) AS DeviceNames
    into #temp2
    FROM #temp p1
    GROUP BY
    p1.lPIMFId

  2. Sherry Li
    January 7, 2011 at 5:26 pm | #2

    update a
    set LinkedLPARNames = case when right(b.DeviceNames, 1) = ‘,’ then left(b.DeviceNames, len(b.DeviceNames) – 1) else b.DeviceNames end
    from #detail a join #temp2 b
    on a.lPIMFId = b.lPIMFId

  3. Sherry
    February 2, 2011 at 11:25 pm | #3

    Recently I had to do a similar task to test a failed stored procedure.

    – sup nm is copied from the report
    declare @SUP_NM VARCHAR (max)
    select @SUP_NM = ‘sup_nm_1, sup_nm_2, sup_nm_n’
    –SELECT VALUE FROM dbo.ListToTable(@SUP_NM,’,’)

    – get CCRID in rows; convert CCRID to varchar, otherwise the send part will not work
    –drop table #temp
    SELECT DISTINCT SUP_NM AS TM_NM
    , convert(varchar(20), SUPCCRID) AS CCRID
    , ’1′ as groupid
    into #temp
    FROM DBO.tbl_LOSS_MIT_DAILY_TBLCOLHIERARCHY_HISTORICAL
    where SUP_NM in (SELECT ltrim(rtrim(VALUE)) FROM dbo.ListToTable(@SUP_NM,’,’))

    –now get CCRID in comma-seperated list
    –drop table #temp2
    SELECT p1.groupid
    , ( SELECT CCRID + ‘,’
    FROM #temp p2
    WHERE p2.groupid = p1.groupid
    ORDER BY
    CCRID
    FOR XML PATH(”) ) AS CCRIDs
    into #temp2
    FROM #temp p1
    GROUP BY
    p1.groupid

    select * from #temp2

    Finally, I copied the result from #temp2, and tested my failed procedure.

    execute myProc ’2011-01-01′, ’2011-01-31′, ’700005,700037,700040,….’, ‘LOC1, LOC2′

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 159 other followers

%d bloggers like this: