January 29, 2015 at 9:54 am
Currently I have a table that looks like the one below and I need to concatenate the description column and keep the rest of the row the same.
current:
IDSeq Desc DateOpen DateClose
1 AA description 1 1/1/2015 12/31/2015
1 AB description 2 1/1/2015 12/31/2015
Desired outcome:
ID Desc DateOpen DateClose
1 description 1,description 2 1/1/2015 12/31/2015
January 29, 2015 at 9:57 am
This article will help you to obtain the desired result.
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
January 29, 2015 at 10:28 am
DDL, more sample data and a better description make this easier but here is a possible solution...
-- (1) Sample Data
DECLARE @table TABLE (ID int, Seq char(2), [desc] varchar(50), DateOpen date, dateClose date)
INSERT @table (ID,Seq,[desc],DateOpen,DateClose)
VALUES (1,'AA','description 1', '1/1/2015', '12/31/2015'),
(1,'AB',' description 2', '1/1/2015','12/31/2015'),
(2,'XX', 'description 12', '1/10/2014', '12/1/2014');
--SELECT * FROM @table;
-- (2) Solution
WITH uqvals AS
(
SELECT DISTINCT ID
FROM @table
)
SELECTID,
[Desc] = REPLACE(REPLACE((SELECT '|'+[desc]+'|' FROM @table t WHERE uv.ID = t.ID FOR XML PATH('')),'||',','),'|',''),
DateOpen = (SELECT MIN(DateOpen) FROM @table t WHERE uv.ID = t.ID),
DateClose = (SELECT MAX(DateClose) FROM @table t WHERE uv.ID = t.ID)
FROM uqvals uv;
-- Itzik Ben-Gan 2001
January 29, 2015 at 10:36 am
Thanks for the input. Exactly what I needed. 😀
January 29, 2015 at 11:56 am
Tweaking Alan's code, I can reduce the reads by half by reducing the length of the code. 🙂
WITH uqvals AS
(
SELECT ID,
MIN(DateOpen) DateOpen,
MAX(DateClose) DateClose
FROM @table
GROUP BY ID
)
SELECTID,
[Desc] = REPLACE(REPLACE((SELECT '|'+[desc]+'|' FROM @table t WHERE uv.ID = t.ID FOR XML PATH('')),'||',','),'|',''),
DateOpen,
DateClose
FROM uqvals uv;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply