Sprade distinct values over a different columns

Question

I'm using SQL Server. I have the following table (MyTable):

ID (int)
value (varchar)

I have the following query:

select distinct value
from MyTable

And the output is:

Value_1
Value_2
Value_3
...
...
...
Value_450

I would like to crate the following table (when value = 'value_1' then 1 else o...):

ID | Value_1 | Value_2 | Value_3 | Value_4 | .... | Value_450
1  |   0     |    1    |    0    |   0     | .... |   0
2  |   0     |    0    |    0    |   1     | .... |   0
3  |   1     |    0    |    0    |   0     | .... |   0
4  |   0     |    1    |    0    |   0     | .... |   0
5  |   0     |    0    |    0    |   0     | .... |   1
6  |   1     |    0    |    0    |   0     | .... |   0
7  |   0     |    0    |    0    |   1     | .... |   0
8  |   0     |    0    |    0    |   0     | .... |   1

If the distinct values of value was small, I would use Case statement for such as query. What should I do in this case which I have so many values? Any smart way to do so?

Look up PIVOT, but I'd question the usefulness of having 450 columns! — DavidG, Nov 20 '14 at 13:34

B0Andrew · Accepted Answer · 2014-11-20T19:19:47.390

It can be done with dynamic sql

declare @query varchar(max);
declare @values varchar(max) = null;

with distinctValues as
(
    select distinct cast(value as varchar(20)) as value from myTable
)
select @values = COALESCE(@values + ', ['+ value + ']', '['+ value + ']') 
from distinctValues;

set @query = 'select [id], ' + @values +
'from myTable pivot ( count(value) for value in (' + @values + ')) as pvt';

exec(@query);

To answer your other questions from the comments:

You can insert the result of a dynamic query into a table by using INSERT ... EXEC syntax:

insert into myOtherTable 
/* specify columns here if result does not have the same structure as the table */
exec(@query);

You said that you have multiple (6) columns that have the same role as the value column in your example. I assume that the names of the columns are known and do not vary. So the table structure is:

id, value1, value2, value3, value4, value5, value6

From what I understand the result must look like this:

id, v1, v2, v3, ..., vn

where v1, v2, v3, ..., vn are all the distinct values that can be found on columns value1, value2, value3, value4, value5, value6

In this case you must use an UNPIVOT first:

declare @query varchar(max);
declare @values varchar(max) = null;

with distinctValues as
(
    select distinct cast(value as varchar(20)) as value 
    from myTable
    UNPIVOT
    (
       value for col in (value1, value2, value3, value4, value5, value6)
    ) as upvt
)
select @values = COALESCE(@values + ', ['+ value + ']', '['+ value + ']') 
from distinctValues;



set @query = 
';with myTableUnpivoted as 
( 
   select id, value 
   from myTable 
   UNPIVOT 
   ( 
      value for col in (value1, value2, value3, value4, value5, value6) 
   ) as upvt 
) 
select [id], ' + @values +
'from myTableUnpivoted pivot ( count(value) for value in (' + @values + ')) as pvt';

-- insert into myOtherTable
exec(@query);

Notice that the the UNPIVOT operation is done twice.

You can store the unpivoted table in a temporary table. Check whether this will improve the performance.

WOW, man. Amazing solution. I've just checked it and it's works great! Thanks! Actually i need to run this query on a 6 different columns (Value column is a 6 different columns. Each columns has his own distinct values). Is it possible to use your query for a 6 different columns in one time? — Omri, Nov 20 '14 at 14:25
You have to explain better (maybe with another example) what is your table structure and what is the expected result. — B0Andrew, Nov 20 '14 at 14:32
It's the same structure and the same expected result as in my original post. In this case instead of having 'Value' column in MyTable, i have 6 different Value columns (Value_1, value_2...value_6). Each Value column has it's own distinct values (which can be similar between the columns. For example: Value_1 and Value_2 column can has the same values). If each column has a 50 distinct values, the expected result is a 450 columns. Hope it's clear enough. — Omri, Nov 20 '14 at 14:50
I can use your query 6 times, each time on a different column, and then join the output tables. It would be smarter to use your query for 6 different columns in one time. — Omri, Nov 20 '14 at 14:53
One more thing - I'm having trouble to insert the output into a table. I tried to use "select * into table" but it doesn't works. — Omri, Nov 20 '14 at 15:40

Sprade distinct values over a different columns

1 Answers1

Linked