December 9, 2011 at 6:00 am
Hello All,
I am trying to collapse some results I am getting back from a SQL view into a smaller subset of results where a specific column gets collapsed into a single field but the other columns are preserved and it the collapse is dictated by a couple of key fields. I have read alot of posts and blogs and can't find a solid solution. Basically my data for example looks like this following:
mary id222 a
mary id222 b
mary id222 c
sue id444 x
sue id444 y
sue id444 z
I want the end result to collapse the rows to look like the following
mary id222 a,b,c
sue id444 x,y,z
Any suggestions on the best way to do this? There seems to be alot of fodder on the subject but I can't seem to locate an exact solution. Any opinions would be greatly appreciated!
December 9, 2011 at 6:23 am
BEGIN TRAN
SET NOCOUNT ON
--Create sample data to play with
SELECT name, col1, col2
INTO #yourTable
FROM (SELECT 'mary', 'id222', 'a'
UNION ALL SELECT 'mary', 'id222', 'b'
UNION ALL SELECT 'mary', 'id222', 'c'
UNION ALL SELECT 'sue', 'id444', 'x'
UNION ALL SELECT 'sue', 'id444', 'y'
UNION ALL SELECT 'sue', 'id444', 'z') a(name, col1, col2)
PRINT '========== Table =========='
SELECT * FROM #yourTable
PRINT REPLICATE('=',80)
PRINT '========== Query =========='
SELECT name, col1, STUFF((SELECT ',' + col2
FROM #yourTable r2
WHERE r2.name = r1.name AND r2.col1 = r1.col1
ORDER BY name, col1
FOR XML PATH('')), 1, 1, '') AS newCol
FROM #yourTable r1
GROUP BY name, col1
PRINT REPLICATE('=',80)
ROLLBACK
Returns
========== Table ==========
name col1 col2
---- ----- ----
mary id222 a
mary id222 b
mary id222 c
sue id444 x
sue id444 y
sue id444 z
================================================================================
========== Query ==========
name col1 newCol
---- ----- ---------------------------------------------------------------------
mary id222 a,b,c
sue id444 x,y,z
================================================================================
To make it clearer, the code I used to concatenate the columns was: -
SELECT name, col1, STUFF((SELECT ',' + col2
FROM #yourTable r2
WHERE r2.name = r1.name AND r2.col1 = r1.col1
ORDER BY name, col1
FOR XML PATH('')), 1, 1, '') AS newCol
FROM #yourTable r1
GROUP BY name, col1
December 12, 2011 at 6:33 am
Thanks for the reply! I actually tried a number of methods and the following seemed to work best for me. I ended up ranking my results, targeting a couple of fields, then I used the following code to collapse the column:
SELECT name, id
COALESCE(MAX(CASE WHEN rank = 1 THEN data END), '') +
COALESCE(MAX(CASE WHEN rank = 2 THEN data END), '') +
COALESCE(MAX(CASE WHEN rank = 3 THEN data END), '') AS CollapsedData
FROM Test.dbo.Test_table
GROUP BY name, id
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply