Pivot Query to convert Rows to Columns

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 501
Pivot command will be used to convert rows to columns....

Here the Properties are Length, Height, area and so on. if you wish to display the properties of each part of engine, then we suppose to use PIVOT option...

The below is the SQL code with sample data and expected output

DECLARE @Table1 TABLE(PID int, PNAME varchar(20), PartID int)
insert @Table1
SELECT 0 , 'Length', 1 union all
SELECT 1 , 'Breadth', 1 union all
SELECT 2 , 'Height', 1 union all
SELECT 0 , 'Area', 2 union all
SELECT 1, 'Volume', 2

DECLARE @Table2 TABLE(SampleID int, PID int, Pvalue int, PartID int)
insert @Table2
SELECT 0, 0, 10, 1 union all
SELECT 0, 1, 10, 1 union all
SELECT 0, 2, 10, 1 union all
SELECT 1, 0, 20, 1 union all
SELECT 1, 1, 20, 1 union all
SELECT 1, 2, 20, 1 union all
SELECT 0, 0, 10, 2 union all
SELECT 0, 1, 10, 2

/*
Depending upon the PartID, i must get the following results
PARTID: 1

SampleID Length Breadth Height
-------- ------ ------- ------
0 10 10 10
1 20 20 20

PARTID: 2

SampleID Area Volume
-------- ---- ------
0 10 10

Expected Output:
PartID SampleID Length Breadth Height Area Volume
1 0 10 10 10 NULL NULL
1 1 20 20 20 NULL NULL
2 0 NULL NULL NULL 10 10


*/

--query to get the desired output
; with aCTE
AS
(
SELECT TOP 100 PERCENT
SampleID
,PNAME
,PValue
,T2.PartID
FROM
@table1 T1
inner join @table2 T2
on T1.PID=T2.PID
AND T1.PartID=T2.PartID
ORDER BY T2.PartID,SampleID )
Select PartID,SampleID,[Length],[Breadth],[Height],[Area],[Volume]
from aCTE
PIVOT
(MIN(PValue) FOR [PNAME] IN ([Length],[Breadth],[Height],[Area],[Volume])) as pvt
order by PartID

Comments or Responses

Login to post response