0

--Editing this as my question was marked as a duplicate. I'm having an issue implementing STUFF and the "trick" FOR XML PATH when my query contains a join.

I have a single column that I have been trying to convert to a single column and single row. The data structure returned from the query below is:

col1
1 
2
3

I'd like it to be comma delimited like so: col1 1,2,3

The query I have is as follows:

select 
  u_id
from 
  tabl1 tb
  inner join CTEE f on f.aid = tb.a_id
group by 
  u_id

I tried to incorporate STUFF and a FOR XML PATH in this query but keep failing to do so. Can anyone point me in the right direction. I've done this for multiple columns when concatenating rows into a comma delimited column, but for some reason this is driving me crazy.

Jay
  • 57
  • 1
  • 10
  • https://stackoverflow.com/search?q=%5Bsql-server%5D+group_concat and https://stackoverflow.com/questions/tagged/sql-server+string-aggregation –  Aug 30 '17 at 20:05
  • STUFF and FOR XML PATH are the way to go, though, from your query, I have no idea what your data structure is supposed to look like. Can you please post the data structure and what you would want your data to look like? – Eli Aug 30 '17 at 20:05
  • Eli, I had edited my question. The data structure returned from the query is there. – Jay Aug 31 '17 at 12:51

1 Answers1

0

Try this: 

    create table #tt
    (id int)

    insert into #TT values (1)
    insert into #TT values (2)
    insert into #TT values (3)

    Select stuff((select ',' + convert(Varchar(1), id)
    from #tt
    Order By 1 For XML Path('')),1,1,'')
Egbert
  • 158
  • 4
  • 12
  • Inserting the data into a temp table, for now is my best bet. Your query, however, skips the first two values in the temp table, and leaves off the last two. I had fixed it by removing the order by 1 and changing your convert to a cast. – Jay Aug 31 '17 at 13:09