Check different values ​​in different columns

Question

I am 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 result:

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

I would like to sort the following table (when value = 'value_1', then another 1 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 different value values ​​were small, I would use the Case statement to query. What should I do in this case, when I have so many values? Any reasonable way to do this?

Answer 1

This can be done using 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 other questions from the comments:

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

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

You said that you have several (6) columns that have the same role as the value column in your example. I assume that the column names are known and do not change. Thus, the structure of the table:

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

From what I understand, the result should look like this:

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

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

In this case, you must first use UNPIVOT :

 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); 

Note that the UNPIVOT operation is performed twice.

You can save an unstretched table in a temporary table. Check if performance improves.