Add from one table to anoter group by

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

SQL Server 2008 Forums

Transact-SQL (2008)

Add from one table to anoter group by

Author

Topic

Gerten_Utv
Starting Member

7 Posts

Posted - 2011-12-06 : 05:38:42

Hi I have an tabel Cup
Like

CREATE TABLE Cup (
	CupId	INT
	, CupdivisionId	INT
	, CupdivisionPlace	INT
	)
	GO
	INSERT INTO Cup VALUES (412,3,4)
	INSERT INTO Cup VALUES (412,3,5)
	INSERT INTO Cup VALUES (412,3,6)
	INSERT INTO Cup VALUES (412,7,1)
	INSERT INTO Cup VALUES (412,7,2)
	INSERT INTO Cup VALUES (412,7,3)
	INSERT INTO Cup VALUES (412,8,1)
	INSERT INTO Cup VALUES (412,8,2)
	INSERT INTO Cup VALUES (412,8,3)
	INSERT INTO Cup VALUES (412,9,1)
	INSERT INTO Cup VALUES (412,9,2)
	INSERT INTO Cup VALUES (412,9,3)

But i like to select output like:
412,3,'4,5,6'
412,7,'1,2,3'
412,8,'1,2,3'
412,9,'1,2,3'
Help Wanted
Brg
Gert

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-12-06 : 05:44:26

[code]DECLARE @Cup TABLE
(
CupID INT,
CupDivisionID INT,
CupDivisionPlace INT
)

INSERT @Cup
VALUES (412, 3, 4),
(412, 3, 5),
(412, 3, 6),
(412, 7, 1),
(412, 7, 2),
(412, 7, 3),
(412, 8, 1),
(412, 8, 2),
(412, 8, 3),
(412, 9, 1),
(412, 9, 2),
(412, 9, 3)

-- SwePeso
SELECT c.CupDivisionID,
STUFF(p.Data, 1, 1, '') AS Result
FROM (
SELECT DISTINCT CupDivisionID
FROM @Cup
) AS c
CROSS APPLY (
SELECT ',' + CAST(w.CupDivisionPlace AS VARCHAR(12))
FROM @Cup AS w
WHERE w.CupdivisionID = c.CupDivisionID
ORDER BY w.CupDivisionPlace
FOR XML PATH('')
) AS p(Data)[/code]

N 56°04'39.26"
E 12°55'05.63"

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-06 : 05:44:44

see
http://www.nigelrivett.net/SQLTsql/CSVStringSQL.html

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-12-06 : 05:45:38

[code]DECLARE @Cup TABLE
(
CupID INT,
CupDivisionID INT,
CupDivisionPlace INT
)

INSERT @Cup
VALUES (412, 3, 4),
(412, 3, 5),
(412, 3, 6),
(412, 7, 1),
(412, 7, 2),
(412, 7, 3),
(412, 8, 1),
(412, 8, 2),
(412, 8, 3),
(412, 9, 1),
(412, 9, 2),
(412, 9, 3)

-- SwePeso
SELECT c.CupID,
c.CupDivisionID,
STUFF(p.Data, 1, 1, '') AS Result
FROM (
SELECT DISTINCT CupID,
CupDivisionID
FROM @Cup
) AS c
CROSS APPLY (
SELECT ',' + CAST(w.CupDivisionPlace AS VARCHAR(12))
FROM @Cup AS w
WHERE w.CupID = c.CupID
AND w.CupDivisionID = c.CupDivisionID
ORDER BY w.CupDivisionPlace
FOR XML PATH('')
) AS p(Data)[/code]

N 56°04'39.26"
E 12°55'05.63"

Gerten_Utv
Starting Member

7 Posts

Posted - 2011-12-06 : 05:56:18

Klockrent..
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources