SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help with using pivot or similar?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jollyguy77
Starting Member

4 Posts

Posted - 02/20/2014 :  06:29:50  Show Profile  Reply with Quote
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

Edited by - jollyguy77 on 02/20/2014 06:31:38

stepson
Constraint Violating Yak Guru

Romania
419 Posts

Posted - 02/20/2014 :  07:12:28  Show Profile  Reply with Quote


;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
	





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



S



sabinWeb MCP
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000