在不同的列上覆盖不同的值 [英] Sprade distinct values over a different columns

问题描述

我正在使用SQL Server.我有下表(MyTable):

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

ID (int)
value (varchar)

我有以下查询:

select distinct value
from MyTable 

输出为:

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

我想创建下表(当value ='value_1'时再添加1个其他值...):

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

如果value的不同值很小，我将使用Case语句进行查询.在我有这么多价值观的情况下，我该怎么办?有什么聪明的方法吗?

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?

推荐答案

可以通过动态sql完成

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:

您可以使用INSERT ... EXEC语法将动态查询的结果插入表中:

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

您说您有多个(6)列，它们与示例中的value列具有相同的作用.我假设这些列的名称是已知的，并且没有变化.因此表结构为:

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

其中v1, v2, v3, ..., vn是在列value1, value2, value3, value4, value5, value6

在这种情况下，您必须先使用UNPIVOT:

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

请注意，UNPIVOT操作已执行两次.

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.

这篇关于在不同的列上覆盖不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！