Use XML path in SQL Server 2012 with a temp table?

Question

I am on version 2012 and need to use FOR XML PATH to get a comma separated string for like projects. I have a temp table that I have compiled and would like to combine people based on project numbers. The results combine all people into one list for all projects. I am looking to get specific to project.

select #Team.WBS1, 
    stuff((
        Select ',', #Team.Team as [text()]
        from #Team
        Where #team.WBS1 = #team.wbs1
        for xml path('')
    ), 1, 1, '') 
from #Team

I am getting the same results for every project number.

Project	People
1	john, jim, sean, billy
1	john, jim, sean, billy
1	john, jim, sean, billy
1	john, jim, sean, billy
2	john, jim, sean, billy
3	john, jim, sean, billy
3	john, jim, sean, billy
4	john, jim, sean, billy
4	john, jim, sean, billy

I need results like this

Project	People
1	john, jim, sean, billy
2	billy
3	jim, sean,
4	john, jim, billy

Data looks like this

Project	People
1	john
1	jim
1	sean
1	billy
2	billy
3	jim
3	sean
4	john
4	jim, billy

You need to use a different alias for your table in your sub-query. — Dale K
– Dale K, Commented Jul 1, 2024 at 19:59
Yes, but you are trying to correlate your sub-query with your outer query, and because it's the same table you need to differentiate it. This #team.WBS1 = #team.wbs1 is like saying 1=1 because it's not differentiating inner and outer — Dale K
– Dale K, Commented Jul 1, 2024 at 20:06
I assume the last row of your "data looks like this" should actually be 2 rows — Dale K
– Dale K, Commented Jul 1, 2024 at 20:10
Does this answer your question: String_agg for SQL Server before 2017 (Specifically I cover how you do this in my answer.) — Thom A
– Thom A ♦, Commented Jul 1, 2024 at 20:13

siggemannen · Accepted Answer · 2024-07-01 20:03:10Z

1

You need to do some grouping first:

WITH data AS (
    SELECT  *
    FROM    (
    VALUES  (1, N'john')
    ,   (1, N'jim')
    ,   (1, N'sean')
    ,   (1, N'billy')
    ,   (2, N'billy')
    ,   (3, N'jim')
    ,   (3, N'sean')
    ,   (4, N'john')
    ,   (4, N'jim, billy')
) t (Project,People)
)
SELECT  Project
,   STUFF((
    SELECT  ',' + People
    FROM    data dInner
    WHERE   dInner.Project = d.Project
    FOR xml path(''), type
    ).value('text()[1]', 'NVARCHAR(MAX)'), 1, 1, '') AS People
FROM    data d
GROUP BY project

And "join" the subquery table with the main one.

Actually, my advice would be to always use aliases for tables, this avoids a lot of errors with incorrect JOINs.

Output:

Project	People
1	john,jim,sean,billy
2	billy
3	jim,sean
4	john,jim, billy

A little side-note is that it's good to use an ORDER BY when doing the FOR XML thing, so the values are always sorted the same way

answered Jul 1, 2024 at 20:03

siggemannen

10.1k3 gold badges12 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dave82 Over a year ago

I gave a snapshot of the data, there are 312 projects (will get bigger as time goes on) with over 20+ (will grow as new projects get added). Grouping all of them doesnt seem feasable. I would love to use String_Agg but 2012 doesnt suport it.

siggemannen Over a year ago

I just mean you need to add GROUP BY to the outer part, so you don't get duplicates :)

Cetin Basoz · Accepted Answer · 2024-07-01 20:13:44Z

0

Alias the tables with different aliases:

   select distinct t1.WBS1, 
        stuff((
            Select ',', t2.Team as [text()]
            from #Team t2
            Where t2.WBS1 = t1.wbs1
            for xml path('')
        ), 1, 1, '') 
    from #Team t1;

DBFiddle demo

edited Jul 1, 2024 at 20:13

answered Jul 1, 2024 at 20:05

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

1 Comment

Cetin Basoz Over a year ago

There should be a way to force downvoters to make an explanation, especially if it is taking them for months to think about it.

Collectives™ on Stack Overflow

Use XML path in SQL Server 2012 with a temp table?

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related