SQL Server使用数据透视功能重构数据 [英] Sql server use pivot function to restruct data

问题描述

我在SQL Server 2012中有一个数据表，如下所示.我想做的是查询数据，但要重新组织布局.有一个称为因子"的列，其中包含7-8个具有相应值的不同标签(值列).我想要的是具有贯穿各列的因素-请查看所需的输出部分以了解我的意思.编写查询以查看我的数据的最佳方法是什么?

I have a table of data in SQL Server 2012, shown below. What I would like to do is query the data but restructure the layout. There is one column called 'factor' that contain 7-8 different labels which have a corresponding value (value column). What I would like is to have the factors as columns going across - please see the desired output section to see what I mean. What is the best way to write a query to view my data as such?

所需的输出

Desired Output

pdate     abc     def   ghi
1-1-13    1       3     2
2-1-13    1       3     2

表格示例

factor     pdate     value
abc        1-1-13    1
def        1-1-13    3
ghi        1-1-13    2
abc        2-1-13    1
def        2-1-13    3
ghi        2-1-13    2
abc        3-1-13    1
def        3-1-13    3
ghi        3-1-13    2
abc        4-1-13    1
def        4-1-13    3
ghi        4-1-13    2    

推荐答案

outisnihil提到，更好的方法是使用PIVOT

as outisnihil mentioned a better approach would be to use PIVOT

使用PiVOT

  SELECT pdate
        , [abc]
        , [def]
        , [ghi]
    FROM YOUR_table
    PIVOT(SUM([value]) FOR factor IN (
                [abc]
                , [def]
                , [ghi]
                )) AS val

---

使用CASE ... 您可以为每个因子值使用案例，以在输出中生成新列.

---

Using CASE... You can use a case for every factor value to generate a new column in your output.

SELECT pdate
, CASE 
WHEN factor = 'abc' SUM(value) END abc
, CASE 
WHEN factor = 'def' SUM(value) END def
, CASE 
WHEN factor = 'ghi' SUM(value) END ghi 
FROM YOUR_table GROUP BY pdate

这篇关于SQL Server使用数据透视功能重构数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！