How to send column data as a comma separated string from stored procedure?

Question

TWO PART QUESTION

I'll try to explain this as best I can.

PART 1

I have a table that looks like this:

the two last rows are identical exept for the types and UrlId.

I have a SP that I call that executes the following query to get me the data above:

SELECT Urlid, DomainName, OrgId, DomainId, s.TypeId AS TypeId
FROM DomainData d
JOIN SystemUrls s ON s.DomainId = d.Id
WHERE @OrgId IS NULL OR  OrgId = @OrgId

How can I make so that the data looks like?:

Name(varchar)   OrgId(bigint)        DomainId(bigint)  TypeId(int)
Three            556548-4499             71              2,1

PART 2

Assuming that I dont change the above result and decide to change it on the server with a linq-query(C#)

How would a linq-query look like to give me the desiered result?

have you tried anything? In mysql it is called group_concat -> and there are SO questions asking how to do it in sql server. For the linq - have you tried? — Gilad Green, Sep 01 '16 at 14:35
Look into XML Stuff: http://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server — applejacks01, Sep 01 '16 at 14:36
@Gilad Green I have tried a few groupby clauses. i haven not used group_concat and wasn't even aware of it untill i read your comment. my knowledge of lets call the above advance tsql is limited.Edit: i would not post a question simply looking for an answer without attempting to solve it on my own. — ThunD3eR, Sep 01 '16 at 14:37
@Ra3IDeN - That is fine, and I'm happy to help with the stuff but effort from your side must be shown - that is why we ask to post code you've tried — Gilad Green, Sep 01 '16 at 14:38
My question is why do you want to pass delimited values in the first place? They are a pain to work with because you will have to split them apart again anyway. — Sean Lange, Sep 01 '16 at 14:39
@Sean Lange in this scenario i would not have to split it again, i will only show the data as is on the page. — ThunD3eR, Sep 01 '16 at 14:39

score 1 · Accepted Answer · edited May 23 '17 at 12:01

1

For part 1 - then look at this question. Someone answered before me so I won't repeat :)

For part 2 - Split it into 2 parts - the first is to select the joint data and then after having it in memory (ToList()) you can use string.Join to concatenate all the TypeIds

var jointData = (from d in db.DomainData
                 join s in db.SystemUrls on d.Id equals s.DomainId into sg
                 new { m.DomainName, m.OrgId, DomainId = m.Id, s.TypeId }).ToList();

var result = jointData.GroupBy(item => new { item.DomainName, item.OrgId, item.DomainId })
             .Select(g => new { 
                 g.Key.DomainName, 
                 g.Key.OrgId, 
                 g.Key.DomainId, 
                 TypeId = string.Join(", ", g.Select(item => item.TypeId))
             });

edited May 23 '17 at 12:01

Community

1
1

answered Sep 01 '16 at 14:45

Gilad Green

36,708
7
61
95

What would happen if you tried to run that in one shot? I.e do the GroupBy/Select before the call to ToList. My only guess why you broke it up into 2 parts is because string.Join won't have a translation to SQL and it'd throw an exception – applejacks01 Sep 01 '16 at 14:51
This worked like a charm. I am experimenting with XML STUFF for the tsql soloution but this is great. Thank you! – ThunD3eR Sep 01 '16 at 15:38
@applejacks01 - So I didn't test it in one go because I currently don't have a database to test it against but from what I know - ya it will fail on not knowing how to perform the string.join – Gilad Green Sep 01 '16 at 15:42
@Ra3IDeN - you are welcome :) In my opinion if you have the stuff mapped and it is select operations then use linq – Gilad Green Sep 01 '16 at 15:43

score 1 · Answer 2 · edited Sep 01 '16 at 20:43

1

See if this works for you:
Note: I'm considering Urlid, DomainName, OrgId, DomainId belongs to "DomainData " and Id , TypeId belongs to "SystemUrls" table.

    SELECT  DomainName, OrgId, Id, 
    STUFF(( SELECT  ', ' + cast(s2.TypeId as varchar(5))
                    FROM    SystemUrls AS s2 
                    WHERE   s2.DomainId = d.Id
                    FOR XML PATH('')
                ), 1, 2, '') as TypeId

    FROM DomainData d
    WHERE @OrgId IS NULL OR  OrgId = @OrgId 
    GROUP BY DomainName, OrgId, Id

edited Sep 01 '16 at 20:43

ThunD3eR

3,216
5
50
94

answered Sep 01 '16 at 16:18

Sagar Shelke

517
3
10

I was pretty close with my first attempt. I was just to stuburn to remove the join that I had which gave me complications. Thank you this worked:) Edited: changes part of it since small parts of your assumption where wrong. – ThunD3eR Sep 01 '16 at 20:37

score -2 · Answer 3 · edited May 23 '17 at 12:24

first create temp table, and declaration with expected range when you want it all in long string format datatype and size may effect so make sure to that.

then,

  insert into #temp(
  Urlid, DomainName, OrgId, DomainId, s.TypeId)
  SELECT Urlid, DomainName, OrgId, DomainId, s.TypeId
  FROM DomainData d
  JOIN SystemUrls s ON s.DomainId = d.Id
  WHERE @OrgId IS NULL OR  OrgId = @OrgId 

 declare @Column1 varchar(350),
 select @Column1= coalesce(@Column1+',','')+convert(varchar(30),TypeId )
 from #temp

update #temp
set Typeid=@column1
from #temp

select * from #temp

by the way this question has been answered previously try not to create duplicate entry this is the link:click here

This sets all the typeIds to the same comma separated value. Which is wrong — ThunD3eR, Sep 01 '16 at 15:37

How to send column data as a comma separated string from stored procedure?

3 Answers3