So I need to straight pivot my data with no aggregation. I have tried PIVOT, UNPIVOT, CROSS APPLY, and CASE statements all driving me crazy.
Here is my current data set
| Header | Contract Value | Total Cost | Profit |
|---|---|---|---|
| Original Budget | 1000 | 900 | 100 |
| Change Orders | 100 | 90 | 90 |
And this is what I would like to do. I want the values in the rows to be the column names.
| Header | Original Budget | Change Orders |
|---|---|---|
| Contract Value | 1000 | 100 |
| Total Cost | 900 | 100 |
| Profit | 100 | 10 |
Can someone please point me in the right direction.
What are you looking is called Fully Trasnpose a table in SQL. it can be done with Unpivot and Pivot or Cross apply and Pivot
your data
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);
using Unpivot and 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
using Cross apply and 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
pivot my data with no aggregationAny particular reason forno aggregation?