sql-server - SQL 行到列 sql

Question

这个问题已经有答案了:

Simple way to transpose columns and rows in SQL? (9 个回答)

已关闭 8 年前。

CallType                Jan   Feb   Mar
vend call Goal >45       0    57    0
OCS call Goal >25        0    13    0
Modified Vend Target     45   38    45
Modified OCS Target      25   25    25

我有这样的表

但是结果我需要像这样显示请帮忙...

CallType  vend call Goal>45 OCS call Goal>25    ModiVTart    ModifOCSTarget
Jan              0             0                     45              25
Feb              57           13                     38              25
Mar              0             0                     45              25 

Answer 1

测试数据

DECLARE @TABLE TABLE 
(CallType VARCHAR(1000), Jan INT,Feb INT,Mar INT)
INSERT INTO @TABLE VALUES
('vend call Goal >45'  ,     0  ,  57  ,  0),
('OCS call Goal >25'   ,     0  ,  13  ,  0),
('Modified Vend Target',     45 ,  38  ,  45),
('Modified OCS Target' ,     25 ,  25  ,  25)

查询

SELECT N AS CallType
      ,[vend call Goal >45]
      ,[OCS call Goal >25]
      ,[Modified Vend Target]
      ,[Modified OCS Target]
FROM @TABLE t 
 UNPIVOT (Vals FOR N IN (Jan,Feb,Mar))up
 PIVOT (SUM(Vals)
        FOR CallType
        IN ([vend call Goal >45],[OCS call Goal >25]
            ,[Modified Vend Target],[Modified OCS Target])
        )p

结果集

╔══════════╦════════════════════╦═══════════════════╦══════════════════════╦═════════════════════╗
║ CallType ║ vend call Goal >45 ║ OCS call Goal >25 ║ Modified Vend Target ║ Modified OCS Target ║
╠══════════╬════════════════════╬═══════════════════╬══════════════════════╬═════════════════════╣
║ Feb      ║                 57 ║                13 ║                   38 ║                  25 ║
║ Jan      ║                  0 ║                 0 ║                   45 ║                  25 ║
║ Mar      ║                  0 ║                 0 ║                   45 ║                  25 ║
╚══════════╩════════════════════╩═══════════════════╩══════════════════════╩═════════════════════╝