I'm trying to get StackOverflow posts along with comments. However it returns each post with the individual comments as a separate record. Something like this -
This leads to redundant records which quickly fill up the 50,000 records at a time that you're allowed to query. How do I combine all the comments into one single field ? Like this-
What you want to do is use the STRING_AGG() function to concatenate the comments together.
For example
Select posts.id, STRING_AGG(comments.text,' | ') from posts
join comments on postid =posts.id
where posts.id = 17406
group by posts.id
The second parameter of the STRING_AGG() function is the separator between the comments.
nvarchar(max) in order for this to work at scale. Otherwise it'll be limited to 8,000 bytes which will error out for any post that has enough comments to exceed that. But this is also somewhat useless for any comments that contain the delimiter already, as you wouldn't be able to separate them again accurately. I believe the asker wanted them in separate columns, probably because they still wanted the comments separated for some purpose.
– animuson♦
Sep 5 at 4:17
You can create a pivot query. The first column will be the postid and then every next column will hold the comments. To make this work you'll need as much columns as the maximum number of comments under one post. This query
select max([commentcount]) [max comment count]
from posts
shows we need 157 columns to cover all those comments.
Here is the SEDE query that returns that result for you:
select postid, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52],[53],[54],[55],[56],[57],[58],[59],[60],[61],[62],[63],[64],[65],[66],[67],[68],[69],[70],[71],[72],[73],[74],[75],[76],[77],[78],[79],[80],[81],[82],[83],[84],[85],[86],[87],[88],[89],[90],[91],[92],[93],[94],[95],[96],[97],[98],[99],[100],[101],[102],[103],[104],[105],[106],[107],[108],[109],[110],[111],[112],[113],[114],[115],[116],[117],[118],[119],[120],[121],[122],[123],[124],[125],[126],[127],[128],[129],[130],[131],[132],[133],[134],[135],[136],[137],[138],[139],[140],[141],[142],[143],[144],[145],[146],[147],[148],[149],[150],[151],[152],[153],[154],[155],[156],[157]
from
(
select top 10000
postid
, row_number() over (partition by postid order by id) [num]
, text
from comments
) data
pivot
(
min(text)
for num in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52],[53],[54],[55],[56],[57],[58],[59],[60],[61],[62],[63],[64],[65],[66],[67],[68],[69],[70],[71],[72],[73],[74],[75],[76],[77],[78],[79],[80],[81],[82],[83],[84],[85],[86],[87],[88],[89],[90],[91],[92],[93],[94],[95],[96],[97],[98],[99],[100],[101],[102],[103],[104],[105],[106],[107],[108],[109],[110],[111],[112],[113],[114],[115],[116],[117],[118],[119],[120],[121],[122],[123],[124],[125],[126],[127],[128],[129],[130],[131],[132],[133],[134],[135],[136],[137],[138],[139],[140],[141],[142],[143],[144],[145],[146],[147],[148],[149],[150],[151],[152],[153],[154],[155],[156],[157])
) pvt
order by postid
This is an example of the output:
I think for performance reasons you best limit the inner query to decent number of rows. Trying to get all of them in one go is not going to fly anyway.
