您正在寻找的是SQL中的Fully Trasnpose
表。可以用Unpivot
和Pivot
,也可以用Cross apply
和Pivot
你的数据
CREATE TABLE mytable(
Header VARCHAR(100) NOT NULL
,Contract_Value INTEGER NOT NULL
,Total_Cost INTEGER NOT NULL
,Profit INTEGER NOT NULL
);
INSERT INTO mytable
(Header,Contract_Value,Total_Cost,Profit) VALUES
('Original Budget',1000,900,100),
('Change Orders',100,90,90);
使用Unpivot
和Pivot
SELECT
name AS Header,
[Original Budget],
[Change Orders]
FROM
(
select
Header,
name,
value
from
mytable unpivot (
value for name in (
[Contract_Value], [Profit], [Total_Cost]
)
) unpiv
) Src PIVOT (
MAX(value) FOR Header IN (
[Original Budget], [Change Orders]
)
) Pvt
ORDER BY
[Original Budget] desc
使用Cross apply
和Pivot
select name as header,
[Original Budget],
[Change Orders]
from
(
select Header,name,value1
From mytable
Cross Apply ( values ('Contract_Value',Contract_Value)
,('Total_Cost',Total_Cost)
,('Profit',Profit)
) B (name,value1)
) src
pivot
(
max(value1)
for Header in ([Original Budget], [Change Orders])
) piv
order by [Original Budget] desc
dbfiddle