-2

I need to pivot one column, in a 2 column result set. For e.g.

enter image description here

The number of email address per Id can vary. Not sure if PIVOT would work for this requirement.

I cannot use a temp table, only a single select query

David
  • 1,147
  • 4
  • 17
  • 29
Rrmave
  • 9
  • 1
  • 1
    please share sample data as text and expected output as text – TheGameiswar Dec 21 '16 at 14:15
  • 1
    If the number of email addresses, and therefore columns in your expected output, can vary, then you'd probably need dynamic SQL for this. But more important, what value would you be using to pivot? – Tim Biegeleisen Dec 21 '16 at 14:17
  • This looks like the typical dynamic pivot or dynamic crosstab to me. There are been hundreds if not thousands of examples of how to do this just in SO. And countless others around the internet. – Sean Lange Dec 21 '16 at 14:21
  • Hi Gameiswar, I tried submitting as text but had problems formatting. what I am looking to do is to convert – Rrmave Dec 21 '16 at 14:21
  • So post your sample data as insert statements to a temp table. – Sean Lange Dec 21 '16 at 14:23
  • 4
    Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) – Sean Lange Dec 21 '16 at 14:46
  • your requirement is unclear , can you provide a sample input and output? – Arunprasanth K V Dec 21 '16 at 18:29

1 Answers1

0

In SQL Server you can use cross apply combined with for xml path('') for this purpose:

  select distinct t1.Id, t3.EmailAddress 
  from TABLE t1
  cross apply (select t2.EmailAddress+'    ' 
               from TABLE t2 
               where t2.Id=t1.Id for xml path('')
              ) t3 (EmailAddress)
  order by t1.Id
Mariusz
  • 66
  • 3