将列的结果转换为单行 [英] Converting the results of a column into a single row

问题描述

我有下表:

Name           Rating
Engineering    1
Financials     3
Scope          1
Schedule       2
Risks          3
People         3

我希望输出如下:

Engineering  Financials  Scope  Schedule  Risks  People
1            3           1      2         3      3

仅使用SQL查询.有人可以帮助我获得正确的输出吗?

Using SQL query only. Can someone help me to get the correct output?

推荐答案

您正在尝试

You are trying to PIVOT the data. SQL server has a PIVOT function that can perform this for you. To perform the PIVOT you need to decide what aggregate function to use. In my sample, I used MAX() but you can use SUM(), etc.

如果没有枢轴函数，则可以将聚集函数与CASE语句一起使用.

If you do not have a pivot function then you can can use an aggregate function with a CASE statement to do this.

聚合/大写版本:此版本要求您将所有名称硬编码到列中.

Aggregate/CASE version: This version requires that you hard-code all of the names into the columns.

select 
  max(case when name = 'Engineering' then rating end) Engineering,
  max(case when name = 'Financials' then rating end) Financials,
  max(case when name = 'Scope' then rating end) Scope,
  max(case when name = 'Schedule' then rating end) Schedule,
  max(case when name = 'Risks' then rating end) Risks,
  max(case when name = 'People' then rating end) People
from yourtable

请参见带有演示的SQL提琴

静态PIVOT版本::您会将名称的值硬编码到该查询中

Static PIVOT version: You will hard code the values of the names into this query

select *
from
(
  select name, rating
  from yourtable
) src
pivot
(
  max(rating)
  for name in ([Engineering], [Financials], [Scope],
               [Schedule], [Risks], [People])
) piv

请参见带有演示的SQL提琴

如果您具有已知的列数，则上述版本非常有用，但是如果您的name值未知，则可以使用动态sql来PIVOT数据.

The above versions work great if you have a known number of columns, but if your name values are unknown, then you can use dynamic sql to PIVOT the data.

动态PIVOT版本:

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

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

set @query = 'SELECT ' + @cols + ' from 
             (
                select name, rating
                from yourtable
            ) x
            pivot 
            (
                max(rating)
                for name in (' + @cols + ')
            ) p '

execute(@query)

请参见带有演示的SQL提琴

所有三个版本将产生相同的结果:

All three versions will produce the same result:

| ENGINEERING | FINANCIALS | SCOPE | SCHEDULE | RISKS | PEOPLE |
----------------------------------------------------------------
|           1 |          3 |     1 |        2 |     3 |      3 |

这篇关于将列的结果转换为单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！