CODEWITHSUNDEEP
sqlsql-server
Relevant
Relevant

Reputation: 169

Problems with using STUFF

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

Upvotes: 0

Views: 76

Answers (3)

square_particle
square_particle

Reputation: 524

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

  1. 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.
  2. 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.

Upvotes: 1

Serg
Serg

Reputation: 22811

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

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

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.

Upvotes: 1

Related Questions