5

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 -

  1. ID Post Comment1
  2. ID Post Comment2
  3. ID Post Comment3

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-

  1. ID Post Comment1 Comment2 Comment3
  • If you need that much data, you should be focusing more on what the ultimate goal is that you're trying to complete rather than trying to do weird things SQL isn't really meant to do. 50,000 records is a LOT of data. Have you considered downloading the data dump instead? What are you actually trying to do with this data? There is certainly a better way to do it. – animuson Sep 5 at 3:56
  • @animuson I suppose the data dump is a cleaner solution. Thanks! – anirudh Sep 5 at 16:22
3

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

Run example

The second parameter of the STRING_AGG() function is the separator between the comments.

  • 2
    You need to cast the comment text to 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
2

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:

postid with many columns

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .