0

I need to convert the column values from a set of rows into a single comma separated string. So how can I transform this:

DtCreated               | UserName | Question          | Answer
2016-09-24 14:30:11.927 | mauricio | Upload 2 images:  | image1.jpeg
2016-09-24 14:30:11.927 | mauricio | Upload 2 images:  | image2.jpeg
2016-11-08 13:59:00.000 | mauricio | What's your name? | Mark
2016-11-08 13:59:00.000 | mauricio | What's your name? | Vivian
2016-11-08 15:37:26.000 | alex     | How old are you?  | 18
2016-11-08 15:37:26.000 | alex     | How old are you?  | 14

Into this: 

DtCreated               | UserName | Question          | Answer
2016-09-24 14:30:11.927 | mauricio | Upload 2 images:  | image1.jpeg,image2.jpeg
2016-11-08 13:59:00.000 | mauricio | What's your name? | Mark,Vivian
2016-11-08 15:37:26.000 | alex     | How old are you?  | 18,14

This is my query:

SELECT 
 sf.DtCreated as [Data da Submissão],    
 sf.UserName as Usuário,
 fc.Title as Question,
 sv.Value + sv.Text as Answer
  FROM [form].SubmissionForm sf
  inner join [form].Submission s on
  sf.id = s.SubmissionFormId
  inner join [form].FormComponent fc on s.FormComponentId = fc.Id
  inner join [form].SubmissionValue sv on s.Id = sv.Submission_Id
  where sf.Form_Id = '31a224f2-5944-46d5-bf8f-d10cadb5a4b5'
  order by sf.DtCreated,sf.UserName,s.subFormNumber,fc.[order] asc
thiago
  • 377
  • 5
  • 20
  • 2
    Found a solution here http://stackoverflow.com/questions/6579440/how-to-concatenate-all-strings-from-a-certain-column-for-each-group – Acewin Nov 08 '16 at 21:03
  • this is supposed to be SQL Server specific. Interestingly as Jeroen pointed solution may vary between different databases – Acewin Nov 08 '16 at 21:05
  • @Jeroen, nice discussion suggested – Acewin Nov 08 '16 at 21:08

0 Answers0