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
测试数据
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 ║
╚══════════╩════════════════════╩═══════════════════╩══════════════════════╩═════════════════════╝