SQL中的Pivot 3列

Question

嗨，我有一个有1.4M条记录的表。表结构如下

Col_1 INT, -- this column has post Codes
Col_2 VARCHAR, -- This Column Shop Names 
Col_3 MONEY, -- this column has distance from every Shop Name to post code with in 50 KM radius.

Total distinct post shop names are = 350
Total Distinct post codes are = 50,000

我想要旋转数据，以便在excel中轻松使用(以矩阵形式)，其中1列有所有的邮政编码，第1行有所有的店铺名称，在邮政编码和店铺名称的交叉点是相应的距离。我可以使用powerpivot来透视数据，但由于记录数的原因，它每次都会崩溃。

表Ex：

1. Col_1    Col_2  Col_3
2. 521025   Rams   7
3. 526256   Rams   8
4. 521025   SPhi   9
5. 526252   pho    10

所需输出

1. PostCode Rams  Sphi  Pho
2. 521025   7     9     0
3. 526256   8     0     10

我如何在SQL中实现这一点呢？

Answer 1

这种从行到列的转换称为PIVOT。在SQL Server 2005+中添加了一个可执行此轮换的函数。

如果你提前知道了这些值，那么你可以使用一个静态的pivot来硬编码它们：

select col_1, 
  IsNull(Rams, 0) Rams, 
  IsNull(Sphi, 0) Sphi, 
  IsNull(pho, 0) Pho
from
(
  select col_1, col_2, col_3
  from yourtable
) src
pivot
(
  sum(col_3)
  for col_2 in (Rams, Sphi, Pho)
) piv

参见SQL Fiddle with Demo。

但是，如果需要转置为列的值数量未知，则可以使用动态sql来执行此操作：

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

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

select @colsNull = STUFF((SELECT distinct ', IsNull(' + QUOTENAME(col_2)+', 0) as '+QUOTENAME(col_2)
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT col_1,' + @colsNull + ' from 
             (
                select col_1, col_2, col_3
                from yourtable
            ) x
            pivot 
            (
                sum(col_3)
                for col_2 in (' + @cols + ')
            ) p '

execute(@query)

请参阅SQL Fiddle with Demo

两者的结果都是：

|  COL_1 | PHO | RAMS | SPHI |
------------------------------
| 521025 |   0 |    7 |    9 |
| 526252 |  10 |    0 |    0 |
| 526256 |   0 |    8 |    0 |

Answer 2

请查收

SELECT 
    Col_1 AS PostCode, 
    ISNULL(Rams, 0) Rams, 
    ISNULL(Sphi, 0) Sphi, 
    ISNULL(Pho, 0)Pho
FROM(
    SELECT Col_1, Col_2, Col_3 FROM YourTable
)UP
PIVOT(SUM(Col_3) FOR Col_2 IN (Rams, Sphi, Pho)) AS PVT