0

I m using SQL Server 2008 R2 and have this table:

ID  Phrase
1   smeels
2   like  
3   teen 
4   spirit

But the number of phrases is like about 20 000 and my goal is to "turn it other way" so to get the output like this:

ID Smeels Like Teen Spirit
1   1      0    0     0
2   0      1    0     0
3   0      0    1     0
4   0      0    0     1

Tried to use CASE with Pivot function but I don't know how to handle with big amount of columns. How can I deal with this task?

Thanks in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Keithx
  • 2,994
  • 15
  • 42
  • 71
  • 3
    You can't really have a result set with 20,000 columns, so what you want to do isn't possible. – Gordon Linoff Feb 29 '16 at 21:50
  • Dont look only on pivot function-if it is still a way would be nice – Keithx Feb 29 '16 at 21:52
  • 1
    Have your application do that (it's going to have to iterate through everything one row at a time anyway). – Aaron Bertrand Feb 29 '16 at 21:53
  • If you have ssrs installed then you can do this with a matrix/table and a column group. – Ross Bush Feb 29 '16 at 21:59
  • Take a step back - why are you doing this? Is it for presentation of a report? Is it for fiddling in Excel? A dataset with between 1 and 20,000 columns is of no practical use in a database so this must be for some other purpose – Nick.Mc Feb 29 '16 at 23:05

1 Answers1

0

Theoretically, you would use a pivot. See explanation from bluefeet here: Convert Rows to columns using 'Pivot' in SQL Server

However, as Gordon Linoff said, you cannot do this with 20,000 columns.`

Community
  • 1
  • 1
E. Monk
  • 378
  • 3
  • 11