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

And this is what I would like to do. I want the values in the rows to be the column names.

Can someone please point me in the right direction.

CodePudding user response：

To pivot your data without aggregation, you can use the PIVOT operator in SQL. The PIVOT operator allows you to transform rows into columns, and specify the values that should be used as column names.

Here is an example of how you could use the PIVOT operator to transform your data:

SELECT *
FROM yourTable
PIVOT
(
  MAX(ContractValue)
  FOR Header IN ([Original Budget], [Change Orders])
) AS PivotTable

In this query, the PIVOT operator is used to transform the rows in the yourTable table into columns. The MAX function is used to specify the values that should be used as column names, and the FOR clause specifies the values that should be used as the column names in the output. In this case, the column names are the values in the Header column.

This query will produce the following output:

You can adjust the column names and values in the PIVOT operator to produce the output that you need. For more information on using the PIVOT operator, you can refer to the documentation for your database management system (DBMS).

CodePudding user response：

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