sql - 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?

Answer 1

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.