July 30, 2010 at 10:06 am
Below link is a Jing screenshot of what I'm looking at.
http://screencast.com/t/MTEwYzY0
I have a Project2User relation table, and a Users table. I'm trying to pull all of the information relating to which users are on which projects (there can be multiple projects per user and multiple users per project).
What do I need to GROUP BY PROJECT2USER.PROJECTID and have the second column be a concatenation of all of the users that are on that project? I want the statement to return:
http://screencast.com/t/YjFkNDQx
July 30, 2010 at 10:18 am
Try looking at PIVOT & UNPIVOT in BOL, you would then have to concatenate the resulting columns into one.
I'm guessing this is for display purposes so the cocatenation would be best handled in the front end if possible.
July 30, 2010 at 10:27 am
This is mostly for display purposes, as I'm doing a mini data dump for a customer and trying to make it as easy as possible for them when they get it. I haven't used PIVOT before but I'll look into it.
Thanks 😀
August 2, 2010 at 3:26 am
--Start by making some test data
DECLARE @TABLE AS TABLE(
PROJECTID INT,
USERID INT)
INSERT INTO @TABLE(PROJECTID,USERID)
SELECT 11111, 1111
UNION ALL SELECT 11111, 1112
UNION ALL SELECT 11111, 1113
UNION ALL SELECT 11112, 1111
UNION ALL SELECT 11112, 1112
--Now to the query
;WITH cte ( PROJECTID, [USERID List], USERID, length )
AS (SELECT PROJECTID,
CAST('' AS VARCHAR(8000)),
CAST('' AS VARCHAR(8000)),
0
FROM @TABLE
GROUP BY PROJECTID
UNION ALL
SELECT p.PROJECTID,
CAST([USERID List] + CASE
WHEN length = 0 THEN ''
ELSE ', '
END + (CAST (p.USERID AS VARCHAR(8000))) AS VARCHAR(8000)),
CAST(p.USERID AS VARCHAR(8000)),
length + 1
FROM cte c
INNER JOIN @TABLE p
ON c.PROJECTID = p.PROJECTID
WHERE p.USERID > c.USERID)
SELECT PROJECTID,
[USERID List] AS [USERID]
FROM (SELECT PROJECTID,
[USERID List],
Rank() OVER ( PARTITION BY PROJECTID ORDER BY length DESC )
FROM cte) d ( PROJECTID, [USERID List], rank )
WHERE rank = 1
Version 2 (thanks to Lowell)
--Start by making some test data
DECLARE @TABLE AS TABLE(
PROJECTID INT,
USERID INT)
INSERT INTO @TABLE(PROJECTID,USERID)
SELECT 11111, 1111
UNION ALL SELECT 11111, 1112
UNION ALL SELECT 11111, 1113
UNION ALL SELECT 11112, 1111
UNION ALL SELECT 11112, 1112
SELECT PROJECTID,
Stuff((SELECT ',' + ( CAST (USERID AS VARCHAR(8000)) )
FROM @TABLE s2
WHERE s2.PROJECTID = s1.PROJECTID
ORDER BY USERID
FOR XML PATH('')), 1, 1, '') AS [USERID]
FROM @TABLE s1
GROUP BY s1.PROJECTID
ORDER BY s1.PROJECTID
August 2, 2010 at 12:58 pm
This definitely works and does what I was looking for. I'm having difficulty figuring out how exactly this works though. Any help explaining this?
Thank you!!!
August 2, 2010 at 1:58 pm
http://www.kodyaz.com/articles/concatenate-using-xml-path.aspx
Figured it out. Thanks again! 🙂
August 3, 2010 at 8:34 am
Glad I could help.
Seems to be a really common question on here, I think I've answered it 3 times with the CTE so far with the last time having Lowell post the XML route which I've added to my little bag of tricks (code snippets).
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply