CODEWITHSUNDEEP
sqlsql-serverazure-sql-database
Suraj
Suraj

Reputation: 675

Azure SQL Transpose a table with all rows

I use Azure SQL database. I have a table - test_excel_poc_head with the below values which I want to transpose using link id values as the columns

enter image description here

The intended output is below where the column is the 'link_id' values. The link_id values are dynamic

enter image description here

I started using UNPIVOT and PIVOT option and below is my unpivot query and results:

SELECT link_id,head_values 
FROM 
(SELECT link_id,comp1,comp2,comp3,comp4
 FROM [dbo].[test_excel_poc_head]
) AS cp
UNPIVOT
(head_values FOR head_value in (comp1,comp2,comp3,comp4)
) AS up

RESULTS:

enter image description here

Now when I add the PIVOT code, it expects an aggregate function which I do not have as it is a string and it errors out. If I add MAX as the aggregate function, I do not get the intended result.

SELECT * FROM (
SELECT link_id,head_values 
FROM 
(SELECT link_id,comp1,comp2,comp3,comp4
 FROM [dbo].[test_excel_poc_head]
) AS cp
UNPIVOT
(head_values FOR head_value in (comp1,comp2,comp3,comp4)
) AS up
) temp_results
PIVOT(
MAX(head_values)
FOR link_id
IN (
[1],[2],[3],[4],[5],[6]
)
) AS PivotTable

RESULT: enter image description here

But this is not my expected result. Is there any other option to achieve PIVOT without the use of agg functions? Thanks for your time and help.

Upvotes: 1

Views: 1045

Answers (1)

Sujitmohanty30
Sujitmohanty30

Reputation: 3316

I tried my luck. Could you check below query if it works,

What I did different to your query is making the result of UNPIVOT distinct by adding row_number to it so that the later PIVOT will take max of each row and display separately. My bad if the explanation doesn't makes sense to you.

select [1],[2],[3],[4],[5],[6] 
  from 
  ( select link_id,head_values,
           row_number() over (partition by link_id order by link_id) rn
      from
         ( select link_id
                 ,cast(comp1 as varchar(255)) as comp1
                 ,cast(comp2 as varchar(255)) as comp2
                 ,cast(comp3 as varchar(255)) as comp3
                 ,cast(comp4 as varchar(255)) as comp4
             from [dbo].[test_excel_poc_head]
         ) as cp
    unpivot
    (
      head_values for head_value in (comp1,comp2,comp3,comp4)
    ) as up
   ) temp_results
pivot
(
  max(head_values)
  for link_id in ([1],[2],[3],[4],[5],[6])
) as pivottable;

db<>fiddle for your reference.

Upvotes: 2

Related Questions