根据某些ID将表的行显示为列 [英] showing rows of table as columns based on some ID

问题描述

我有一个类似

 ID     option
 1      optionA
 1      optionB
 1      optionC
 1      optionD

我想要一个类似的结果

ID    A        B       C       D
1  optionA  optionB optionC optionD

做到这一点的最佳方法是什么?

What is the best way to do this?

我尝试过的查询是

select * from TableName PIVOT (option for ID = 2674 )) as abc 

这将不起作用，因为PIVOT需要聚合函数.

this will not work since PIVOT expects aggregated function..

我也尝试过这样的COALESCE

I have also tried COALESCE like this

declare @t table(num VARCHAR(100))
insert into @t 
    select choice FROM QuestionAnswers where QuestionID=2674
select num from @t
declare @s varchar(8000)
select  @s = COALESCE(@s + ',', '') + num
from    @t
exec('select '+@s)

但这不能正常工作.

推荐答案

这种数据转换类型称为数据点.在SQL Server 2005+中，有一个功能可以为您执行此数据轮换.但是，您可以通过多种方式执行此数据转换.

This type of data transformation is known as a pivot. In SQL Server 2005+ there is a function that will perform this data rotation for you. However, there are many ways that you can perform this data transformation.

这是一个PIVOT查询，它将与您的示例数据一起使用:

Here is a PIVOT query that will work with your sample data:

select *
from
(
  select id, [option], right([option], 1) col
  from yourtable
) src
pivot
(
  max([option])
  for col in (a, b, c, d)
) piv

请参见带演示的SQL提琴.

这也可以使用带有CASE表达式的聚合函数来执行:

This can also be performed using an aggregate function with a CASE expression:

select id,
  max(case when col = 'a' then [option] else null end) a,
  max(case when col = 'b' then [option] else null end) b,
  max(case when col = 'c' then [option] else null end) c,
  max(case when col = 'd' then [option] else null end) d
from
(
  select  id, [option], right([option], 1) col
  from yourtable
) src
group by id

请参见带演示的SQL提琴.

您可以在表上执行多个联接:

You can perform multiple joins on your table:

select a.id,
  a.[option] a,
  b.[option] b,
  c.[option] c,
  d.[option] d
from yourtable a
left join yourtable b
  on a.id = b.id
  and right(b.[option], 1) = 'b'
left join yourtable c
  on a.id = c.id
  and right(c.[option], 1) = 'c'
left join yourtable d
  on a.id = d.id
  and right(d.[option], 1) = 'd'
where right(a.[option], 1) = 'a'

请参见带演示的SQL提琴

最后，如果要转换为列的值未知，则可以使用动态sql来完成:

Lastly, this can be done using dynamic sql if the values to be turned into columns is unknown:

DECLARE @colsName AS NVARCHAR(MAX),
    @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @colsName = STUFF((SELECT distinct ', ' + QUOTENAME(right([option], 1)) +' as '+ right([option], 1)
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @cols = STUFF((SELECT distinct ', ' + QUOTENAME(right([option], 1))
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT id, ' + @colsName + ' from 
             (
                select id, [option], right([option], 1) col
                from yourtable
            ) x
            pivot 
            (
                max([option])
                for col in (' + @cols + ')
            ) p '

execute(@query)

请参见带有演示的SQL小提琴

所有查询的结果是:

| ID |       A |       B |       C |       D |
----------------------------------------------
|  1 | optionA | optionB | optionC | optionD |

这篇关于根据某些ID将表的行显示为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！