CODEWITHSUNDEEP
sqlsql-serverpivot
jeff
jeff

Reputation: 101

Pivot SQL data without aggregation

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.

Upvotes: 1

Views: 131

Answers (1)

RF1991
RF1991

Reputation: 2265

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

dbfiddle

Upvotes: 1

Related Questions