Multiple rows on one select statement

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

General SQL Server Forums

New to SQL Server Programming

Multiple rows on one select statement

Author

Topic

ttran
Starting Member

23 Posts

Posted - 2011-04-04 : 14:24:32

I've tried 3 hrs and I can't make it work.
Could someone help me with this, please?

I have a table:

ID PT RS SS PL
10 1 5 10 15
10 2 6 11 16
10 3 7 12 18
11 2 8 18 19
11 3 5 10 15
12 1 9 12 14
12 3 7 12 18

I like to have:

ID PT1 RS1 SS1 PL1 PT2 RS2 SS2 PL2 PT3 RS3 SS3 PL3
10 1 5 10 15 2 6 11 16 3 7 12 18
11 2 8 18 19 3 5 10 15
12 1 9 12 14 3 7 12 18

Very appreciate if someone could help me or point me to the right direction.

ttran

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-04-04 : 14:37:45

I don't see much option but to declare a temp table that mimics the structure you want and then do an initial populate with DISTINCT IDs only, then do updates on each group you want to capture.

Hey, it compiles.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-04 : 14:40:15

Not sure which way you want to go... and your spacing was unclear...

but one of these should help:


Declare @t table(
	ID int,
	PT int, 
	RS int,
	SS int, 
	PL int
)
Insert Into @t Select 10, 1, 5, 10, 15
Insert Into @t Select 10, 2, 6, 11, 16
Insert Into @t Select 10, 3, 7, 12, 18
Insert Into @t Select 11, 2, 8, 18, 19
Insert Into @t Select 11, 3, 5, 10, 15
Insert Into @t Select 12, 1, 9, 12, 14
Insert Into @t Select 12, 3, 7, 12, 18


Select 
	A.ID,
	PT1,RS1,SS1,PL1,
	PT2,RS2,SS2,PL2,
	PT3,RS3,SS3,PL3
From (Select distinct ID From @t) A
Left Join 
	(
	Select ID, PT1 = PT, RS1 = RS, SS1 = SS, PL1 = PL 
	From @t
	Where PT = 1
	) B
On A.id = B.id
Left Join 
	(
	Select ID, PT2 = PT, RS2 = RS, SS2 = SS, PL2 = PL 
	From @t
	Where PT = 2
	) C
On A.id = C.id
Left Join 
	(
	Select ID, PT3 = PT, RS3 = RS, SS3 = SS, PL3 = PL 
	From @t
	Where PT = 3
	) D
On A.id = D.id


Select 
	ID,
	PT1 = sum(case PT when 1 then PT else null end),
	RS1 = sum(case PT when 1 then RS else null end),
	SS1 = sum(case PT when 1 then SS else null end),
	PL1 = sum(case PT when 1 then PL else null end),
	PT2 = sum(case PT when 2 then PT else null end),
	RS2 = sum(case PT when 2 then RS else null end),
	SS2 = sum(case PT when 2 then SS else null end),
	PL2 = sum(case PT when 2 then PL else null end),
	PT3 = sum(case PT when 3 then PT else null end),
	RS3 = sum(case PT when 3 then RS else null end),
	SS3 = sum(case PT when 3 then SS else null end),
	PL3 = sum(case PT when 3 then PL else null end)
From @t
Group By ID


;with cte(ID, PT, RS, SS, PL, RankNum)
As (
	Select 
		*,
		RankNum = Row_Number() Over(Partition By ID Order By PT)
	From @t
)

Select 
	A.ID,
	PT1,RS1,SS1,PL1,
	PT2,RS2,SS2,PL2,
	PT3,RS3,SS3,PL3
From (Select distinct ID From @t) A
Left Join 
	(
	Select ID, PT1 = PT, RS1 = RS, SS1 = SS, PL1 = PL 
	From cte
	Where RankNum = 1
	) B
On A.id = B.id
Left Join 
	(
	Select ID, PT2 = PT, RS2 = RS, SS2 = SS, PL2 = PL 
	From cte
	Where RankNum = 2
	) C
On A.id = C.id
Left Join 
	(
	Select ID, PT3 = PT, RS3 = RS, SS3 = SS, PL3 = PL 
	From cte
	Where RankNum = 3
	) D
On A.id = D.id



Select 
	ID,
	PT1 = sum(case RankNum when 1 then PT else null end),
	RS1 = sum(case RankNum when 1 then RS else null end),
	SS1 = sum(case RankNum when 1 then SS else null end),
	PL1 = sum(case RankNum when 1 then PL else null end),
	PT2 = sum(case RankNum when 2 then PT else null end),
	RS2 = sum(case RankNum when 2 then RS else null end),
	SS2 = sum(case RankNum when 2 then SS else null end),
	PL2 = sum(case RankNum when 2 then PL else null end),
	PT3 = sum(case RankNum when 3 then PT else null end),
	RS3 = sum(case RankNum when 3 then RS else null end),
	SS3 = sum(case RankNum when 3 then SS else null end),
	PL3 = sum(case RankNum when 3 then PL else null end)
From 
	(
	Select 
		*,
		RankNum = Row_Number() Over(Partition By ID Order By PT)
	From @t
	) A	
Group By ID

Corey

I Has Returned!!

ttran
Starting Member

23 Posts

Posted - 2011-04-04 : 15:00:07

I'm sorry...
I didn't make myself clear because the posting didn't do spacing properly.

I like to have the following result:

ID PT1 RS1 SS1 PL1 PT2 RS2 SS2 PL2 PT3 RS3 SS3 PL3
10 1 5 10 15 2 6 11 16 3 7 12 18
11 null null null null 2 8 18 19 3 5 10 15
12 1 9 12 14 null null null null 3 7 12 18

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-04 : 15:13:02

Ok... did you run the script I gave you?...

both of these do that:


Select 
	A.ID,
	PT1,RS1,SS1,PL1,
	PT2,RS2,SS2,PL2,
	PT3,RS3,SS3,PL3
From (Select distinct ID From @t) A
Left Join 
	(
	Select ID, PT1 = PT, RS1 = RS, SS1 = SS, PL1 = PL 
	From @t
	Where PT = 1
	) B
On A.id = B.id
Left Join 
	(
	Select ID, PT2 = PT, RS2 = RS, SS2 = SS, PL2 = PL 
	From @t
	Where PT = 2
	) C
On A.id = C.id
Left Join 
	(
	Select ID, PT3 = PT, RS3 = RS, SS3 = SS, PL3 = PL 
	From @t
	Where PT = 3
	) D
On A.id = D.id


Select 
	ID,
	PT1 = sum(case PT when 1 then PT else null end),
	RS1 = sum(case PT when 1 then RS else null end),
	SS1 = sum(case PT when 1 then SS else null end),
	PL1 = sum(case PT when 1 then PL else null end),
	PT2 = sum(case PT when 2 then PT else null end),
	RS2 = sum(case PT when 2 then RS else null end),
	SS2 = sum(case PT when 2 then SS else null end),
	PL2 = sum(case PT when 2 then PL else null end),
	PT3 = sum(case PT when 3 then PT else null end),
	RS3 = sum(case PT when 3 then RS else null end),
	SS3 = sum(case PT when 3 then SS else null end),
	PL3 = sum(case PT when 3 then PL else null end)
From @t
Group By ID

Corey

I Has Returned!!

ttran
Starting Member

23 Posts

Posted - 2011-04-04 : 15:38:37

It works.
Thanks so much, Corey.

ttran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources