June 5, 2015 at 7:25 am
My table structure is like
col1 col2 col3 col4 col5 col6
abc. def. 3fg. 59j. 567. 596040
abc. def. 3fg. 59j. 567. 596042
abc. def. 3fg. 59j. 567. 596043
abc. def. 3fg. 59j. 567. 596044
edf. ijk. rkl. 1fh. 567. 596045
edf. ijk. rkl. 1fh. 567. 596046
edf. ijk. rkl. 1fh. 567. 596047
edf. ijk. rkl. 1fh. 567. 596048
edf. ijk. rkl. 1fh. 567. 596049
and I am trying to get the above data , gel them ino col 6 by comma separated
col1 col2 col3 col4 col5 col6
abc def 3fg 59j 567 596040,567 596042,567 596043,567 596044
edf ijk rkl 1fh 567 596045,596046,596047,596048,596049
can I get an example query for this , fairly new to SQL any help would be greatly appreciated
thanks
June 5, 2015 at 8:31 am
DECLARE @sampleData TABLE(
col1 VARCHAR(4)
, col2 VARCHAR(4)
, col3 VARCHAR(4)
, col4 VARCHAR(4)
, col5 VARCHAR(4)
, col6 VARCHAR(max)
);
INSERT INTO @sampleData VALUES ('abc.','def.','3fg.','59j.','567.','596040');
INSERT INTO @sampleData VALUES ('abc.','def.','3fg.','59j.','567.','596042');
INSERT INTO @sampleData VALUES ('abc.','def.','3fg.','59j.','567.','596043');
INSERT INTO @sampleData VALUES ('abc.','def.','3fg.','59j.','567.','596044');
INSERT INTO @sampleData VALUES ('edf.','ijk.','rkl.','1fh.','567.','596045');
INSERT INTO @sampleData VALUES ('edf.','ijk.','rkl.','1fh.','567.','596046');
INSERT INTO @sampleData VALUES ('edf.','ijk.','rkl.','1fh.','567.','596047');
INSERT INTO @sampleData VALUES ('edf.','ijk.','rkl.','1fh.','567.','596048');
INSERT INTO @sampleData VALUES ('edf.','ijk.','rkl.','1fh.','567.','596049');
SELECT DISTINCT col1, col2, col3, col4, col5, col6 = STUFF((
SELECT ',' + col5 + ' ' + col6 AS [text()]
FROM @sampleData
WHERE col1 = s.col1
AND col2 = s.col2
AND col3 = s.col3
AND col4 = s.col4
FOR XML PATH(''), TYPE
).value('.','varchar(max)'),1,1,'')
FROM @sampleData AS s
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply