Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
rupaka
Starting Member
4 Posts |
Posted - 2010-11-05 : 16:31:47
|
How to i group and display out put of an SQL ( multiple ) into a single records for eg my records are 001 234 454 test 34 001 234 454 test2 34How do i write an SQl to get output grouped them in single row |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
rupaka
Starting Member
4 Posts |
Posted - 2010-11-05 : 16:44:28
|
My query returns records like thisClA ClB ClC ClD001 abc 456 test001 abc 456 test1001 abc 456 test2001 abc 456 test3i wnated a single record which can actually group all these001 abc 456 test test1 test2 test3or any xml output like..001 abc 456 testtest1test2test3 |
|
|
KlausEngel
Yak Posting Veteran
85 Posts |
Posted - 2010-11-05 : 21:33:33
|
Try something like this:SELECT STUFF((SELECT s2.C1A + ' ' + s2.C1B + ' ' + s2.C1C + ' ' + s1.C1D FROM TableName AS s2 WHERE s2.C1D = s1.C1D FOR XML PATH('')), 1, 1, '') AS EntriesFROM TableName AS s1ORDER BY s1.C1D |
|
|
rupaka
Starting Member
4 Posts |
Posted - 2010-11-07 : 01:15:54
|
Thanks Guys ! |
|
|
glendcruz
Yak Posting Veteran
60 Posts |
Posted - 2010-11-07 : 01:16:42
|
DECLARE @T TABLE ( CIA INT, CIB VARCHAR(10), CIC INT, CID VARCHAR(10) )INSERT INTO @TSELECT 001, 'abc', 456, 'test' UNION ALLSELECT 001, 'abc', 456, 'test1' UNION ALLSELECT 001, 'abc', 456, 'test2' UNION ALLSELECT 001, 'abc', 456, 'test3'select distinct '00'+ cast(j.cia as char(3))as 'cia',j.cib,j.cic , STUFF((SELECT TOP 100 PERCENT ' ' + s2.cid FROM @T AS s2 WHERE s2.cia = j.cia ORDER BY '' + s2.cid FOR XML PATH('') ), 1, 1, '') AS 'All text added'from @t j(4 row(s) affected)cia cib cic All text added----- ---------- ----------- ---------------------001 abc 456 test test1 test2 test3(1 row(s) affected) |
|
|
|
|
|
|
|