Query - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query

Author

Topic

mateenmohd
Constraint Violating Yak Guru

297 Posts

Posted - 2008-06-13 : 09:02:41

I want to generate query which field like this.
Number and text

Sum_ID Text
------------------
111001 SC12-007
111001 SC12-008
111002 SC12-010
111003 SC14-006
111003 SC14-005
111004 SC02-009
111004 SC02-010
111004 SC02-007
111004 SC02-008
111004 SC02-005
111004 SC02-006
111005 SC12-019
111005 SC12-020
111005 SC12-018
111005 SC12-017
111006 SC12-012
111006 SC12-011

Query result/output should be like this

Sum_ID Text
--------------------------
111001 SC12-007, SC12-008
111002 SC12-010
111003 SC14-006, SC14-005
111004 SC02-009, SC02-010, SC02-007, SC02-008, SC02-005, SC02-006
111005 SC12-020, CS12-018, SC12-019, SC12-017
....
..

I use group by on sum_id which output like this
sum_id test
--------------------
111001 SC12-008
..........SC12-007
111002 SC12-010
111003 SC14-006
..........SC14-005
111004 SC02-009
..........SC02-010
..........SC02-007
..........SC02-008
..........S012-005
..........SC02-006
....
..

How can write query the it output should be retrieve as above ?
i.e. it retrieve recods with add comma , in one line ?

Thanks for help
Mateen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-13 : 09:04:02

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-13 : 09:11:08

[code]DECLARE @Sample TABLE (SumID INT, Data CHAR(8))

INSERT @Sample
SELECT 111001, 'SC12-007' UNION ALL
SELECT 111001, 'SC12-008' UNION ALL
SELECT 111002, 'SC12-010' UNION ALL
SELECT 111003, 'SC14-006' UNION ALL
SELECT 111003, 'SC14-005' UNION ALL
SELECT 111004, 'SC02-009' UNION ALL
SELECT 111004, 'SC02-010' UNION ALL
SELECT 111004, 'SC02-007' UNION ALL
SELECT 111004, 'SC02-008' UNION ALL
SELECT 111004, 'SC02-005' UNION ALL
SELECT 111004, 'SC02-006' UNION ALL
SELECT 111005, 'SC12-019' UNION ALL
SELECT 111005, 'SC12-020' UNION ALL
SELECT 111005, 'SC12-018' UNION ALL
SELECT 111005, 'SC12-017' UNION ALL
SELECT 111006, 'SC12-012' UNION ALL
SELECT 111006, 'SC12-011'

SELECT s1.SumID,
STUFF(g.Data, 1, 1, '') AS Data
FROM (
SELECT SumID
FROM @Sample
GROUP BY SumID
) AS s1
CROSS APPLY (
SELECT TOP 100 PERCENT
',' + s2.Data
FROM @Sample AS s2
WHERE s2.SumID = s1.SumID
ORDER BY s2.Data
FOR XML PATH('')
) AS g(Data)
ORDER BY s1.SumID

SELECT CASE p.RecID
WHEN 1 THEN p.SumID
END AS SumID,
p.Data
FROM (
SELECT SumID,
Data,
ROW_NUMBER() OVER (PARTITION BY SumID ORDER BY Data) AS RecID
FROM @Sample
) AS p
ORDER BY p.SumID,
p.Data[/code]

E 12°55'05.25"
N 56°04'39.16"

mateenmohd
Constraint Violating Yak Guru

297 Posts

Posted - 2008-06-13 : 09:30:43

Thanks a lot. Query is very nice.
I have sql 2000 not sql 2005.

Query can be convert to sql 2000 syntax ?

regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 10:24:19

http://www.projectdmx.com/tsql/rowconcatenate.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources