Problems with using STUFF

Question

Why the heck isn't this working??? Seems to follow everything I've found around here. I'm getting the error: Column '#TempTable.clientId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I add the tt.clientId to the group by, then it doesn't do the stuff, and combine them all into 1 line, they come up as separate rows. Did I make a typo or something?

SELECT tt.Station, STUFF((SELECT ', ' + c.client_code 
    FROM client c 
    WHERE tt.clientId = c.ID 
    FOR XML PATH('')),1,1,'') [Values]            
FROM #TempTable tt
GROUP BY tt.Station

Serg · Accepted Answer · 2016-06-24 20:29:15Z

1

SELECT ... FOR XML PATH should be a function of GROUP BY column[s] tt.station in your case. Something like that

SELECT tt.Station, STUFF((SELECT ', ' + c.client_code 
    FROM client c
    JOIN  #TempTable tt2  
       ON tt2.clientId = c.ID 
       AND tt2.Station = tt.Station
    FOR XML PATH('')),1,1,'') [Values]            
FROM
GROUP BY tt.Station

answered Jun 24, 2016 at 20:29

Serg

22.8k5 gold badges23 silver badges50 bronze badges

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

1 Comment

Relevant Over a year ago

Ugh, that was driving me crazy. Makes more sense now, thank you!

Tab Alleman · Accepted Answer · 2016-06-24 20:16:38Z

1

You have to add tt.ClientId to the GROUP BY because you are using it to correlate the subquery here: WHERE tt.clientId = c.ID

Otherwise, how does SQL Server know which ClientId to use for each Station?

If you don't want to group by ClientId, then you have to correlate by tt.Station.

answered Jun 24, 2016 at 20:16

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Comments

square_particle · Accepted Answer · 2016-06-25 01:28:15Z

There are a couple alternatives here, but here are some things to consider:

Determine what you want your left-most item to be -- in this case, it looks like it's supposed to be station. If that is the case, one way to approach it is to first get the distinct set of stations. This can be done using a group by or a distinct.
Determine the level of the items for which you wish to generate a list -- in this case it's client_code. Therefore, you want to get you inner select to be at that level. One way to do that is to resolve the distinct set of client codes prior to attempting to use for xml.

One critique -- it's always good to provide a simple set of data. Makes providing an answer much easier and faster.

Again, there are alternatives here, but here's a possible solution.

The test data

select top (100)
    client_id = abs(checksum(newid())) % 100,
    client_code = char(abs(checksum(newid())) % 10 + 65)
into #client
from sys.all_columns a
cross join sys.all_columns b;

select top (100)
    station = abs(checksum(newid())) % 10,
    client_id = abs(checksum(newid())) % 50 -- just a subset
into #temp
from sys.all_columns a
cross join sys.all_columns b;

The query

select station, client_codes = stuff((
    select ', ' + cc.client_code
    from (
        select distinct c.client_code -- distinct client codes
        from #temp t
        join #client c
            on t.client_id = c.client_id
        where t.station = s.station) cc
    order by client_code
    for xml path('')), 1, 2, '')
from (
    select distinct station
    from #temp) s; -- distinct stations

The results

station     client_codes
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0           B, C, D, E, G, J
1           A, B, D, G, H, J
2           A, C, E, F, G, H, J
3           B, C, H, J
4           A, B, C, D, F, H, J
5           H, J
6           D, E, F, G, I
7           A, C, D, F, G, H, J
8           A, E, G
9           C, E, F, G, I

Hope this helps.

Collectives™ on Stack Overflow

Problems with using STUFF

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related