Help with using pivot or similar? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with using pivot or similar?

Author

Topic

jollyguy77
Starting Member

4 Posts

Posted - 2014-02-20 : 06:29:50

Hi,

First of all, i am new to sql. Here is the sample (for both table1 and table2, i have created a "SNO" as primary key and it's also identity column)

Table1
------
PID PNAME PartID
--- ----- ------
0 Length 1
1 Breadth 1
2 Height 1
0 Area 2
1 Volume 2

Table2
------

SampleID PID Pvalue PartID
-------- --- ------- ------
0 0 10 1
0 1 10 1
0 2 10 1
1 0 20 1
1 1 20 1
1 2 20 1
0 0 10 2
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

How to achieve the desired output as mentioned above in SQL Server 2008?

Thanks,
Jollyguy

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-20 : 07:12:28

[code]

;with Table1
AS
(select 0 PID,'Length' PNAME, 1 PartID union all
select 1, 'Breadth', 1 union all
select 2, 'Height', 1 union all
select 0, 'Area' , 2 union all
select 1, 'Volume', 2 )
,Table2
AS
(select 0 SampleID,0 PID ,10 Pvalue, 1 PartID 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)
,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

[/code]

output
[code]
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
[/code]

S

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources