Pivot? Rank Over? ... - SQL Server Forums

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)

Pivot? Rank Over? ...

Author

Topic

Zifter
Yak Posting Veteran

51 Posts

Posted - 2013-06-11 : 07:09:41

With a situation like the following

drop table #MasterTable
drop table #ChildTable1
drop table #ChildTable2

create table #ChildTable1 (ID int IDENTITY(1,1) not null primary key, 
                           Table1Field1 varchar(50))
create table #ChildTable2 (ID int IDENTITY(1,1) not null primary key,
                           Table2Field1 varchar(50))
create table #MasterTable (ID int IDENTITY(1,1) not null primary key,
                           MasterField1 varchar(50), 
                           FK_Table1 int not null references #ChildTable1(ID), 
                           FK_Table2 int null references #ChildTable2(ID))

insert into #ChildTable1
values
('Alpha'),
('Beta')

insert into #ChildTable2
values
('First'),
('Second')

insert into #MasterTable
values
('X',1,1),
('X',1,2),
('X',2,2),
('Y',1,1),
('Y',1,2),
('Y',2,1),
('Y',2,2),
('Y',2,1)

select mast.MasterField1, child1.Table1Field1, child2.Table2Field1
from #MasterTable as mast
left join #ChildTable1 as child1
  on child1.ID = mast.FK_Table1
left join #ChildTable2 as child2
  on child2.ID = mast.FK_Table2

I get a result like

X	Alpha	First
X	Alpha	Second
X	Beta	Second
Y	Alpha	First
Y	Alpha	Second
Y	Beta	First
Y	Beta	Second
Y	Beta	First

How should the query look like to get the following result?

x	Alpha	First	Second
X	Beta		Second
Y	Alpha	First	Second
Y	Beta	First	Second
Y	Beta	First

I have no idea how to begin...

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-11 : 07:23:28

--may be this?


select mast.MasterField1, child1.Table1Field1
	,MAX(CASE WHEN child2.ID = 1 THEN child2.Table2Field1 END) as Table2Field1
	,MAX( CASE WHEN child2.ID = 2 THEN child2.Table2Field1 END) as Table2Field2
from #MasterTable as mast
left join #ChildTable1 as child1
  on child1.ID = mast.FK_Table1 
left join #ChildTable2 as child2
  on child2.ID = mast.FK_Table2
GROUP BY mast.MasterField1, child1.Table1Field1
ORDER BY 1, 2

--
Chandu

Zifter
Yak Posting Veteran

51 Posts

Posted - 2013-06-11 : 07:33:31

First of all, thank you for your assistance!. It already helps me to think in the right direction.
But the result is not exactly the same as what I've expected.
The 'tricky' record is the duplicated ('Y',2,1).
I was hoping to get it on a new line without a value for the last column...

Edit: after trying to understand how your query works, I'm not sure this is a way I can continue on.
If I understand correctly, you've hard-coded the ID's and do a MAX() on a character field. This might work for the data I've provided in this example, but I'm sure will give strange results when used on my real life data...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 07:42:49

[code]
select MasterField1,Table1Field1,
MAX(CASE WHEN Table2Field1= 'First' THEN Table2Field1 END) AS First,
MAX(CASE WHEN Table2Field1= 'Second' THEN Table2Field1 END) AS Second
from
(
select row_number() over (partition by mast.MasterField1, child1.Table1Field1 order by (select 1)) AS Seq, mast.MasterField1, child1.Table1Field1 ,
child2.Table2Field1
from #MasterTable as mast
left join #ChildTable1 as child1
on child1.ID = mast.FK_Table1
left join #ChildTable2 as child2
on child2.ID = mast.FK_Table2
)t
GROUP BY MasterField1,Table1Field1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Zifter
Yak Posting Veteran

51 Posts

Posted - 2013-06-11 : 08:00:25

Thanks!
But the query gives the same result as Bandi's.
The last 'tricky' record is missing. I would expect 5 rows returned and both your queries return 4...
You do an additional row_number over, but never use it. Could it be that part of your query is missing?

(What does the MAX() on a character field do? Will it take the highest alphabetically ordered or something? Or is it a kind of cheat to bypass the group by?)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 08:07:17

quote:
Originally posted by Zifter

Thanks!
But the query gives the same result as Bandi's.
The last 'tricky' record is missing. I would expect 5 rows returned and both your queries return 4...
You do an additional row_number over, but never use it. Could it be that part of your query is missing?

(What does the MAX() on a character field do? Will it take the highest alphabetically ordered or something? Or is it a kind of cheat to bypass the group by?)

sorry i missed out a col
it should be this


select MasterField1,Table1Field1,
MAX(CASE WHEN Table2Field1= 'First' THEN Table2Field1 END) AS First,
MAX(CASE WHEN Table2Field1= 'Second' THEN Table2Field1 END) AS Second
from
(
select row_number() over (partition by mast.MasterField1, child1.Table1Field1 order by (select 1)) AS Seq, mast.MasterField1, child1.Table1Field1 ,
child2.Table2Field1
from #MasterTable as mast
left join #ChildTable1 as child1
  on child1.ID = mast.FK_Table1
left join #ChildTable2 as child2
  on child2.ID = mast.FK_Table2
)t
GROUP BY MasterField1,Table1Field1,Seq

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Zifter
Yak Posting Veteran

51 Posts

Posted - 2013-06-11 : 08:16:12

I really appreciate all the help and I'm sorry I don't understand what you're trying to do.
But your last query, where you've added the "Seq" as third group by, gives almost the same result as my simple left join query. The only difference is the splitting of the Table2Field1 into 2 columns.
Would you be so kind to have another look? Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 08:19:33

one more attempt


select MasterField1,Table1Field1,
MAX(CASE WHEN Table2Field1= 'First' THEN Table2Field1 END) AS First,
MAX(CASE WHEN Table2Field1= 'Second' THEN Table2Field1 END) AS Second
from
(
select row_number() over (partition by mast.MasterField1, child1.Table1Field1,child2.Table2Field1  order by (select 1)) AS Seq, mast.MasterField1, child1.Table1Field1 ,
child2.Table2Field1
from #MasterTable as mast
left join #ChildTable1 as child1
  on child1.ID = mast.FK_Table1
left join #ChildTable2 as child2
  on child2.ID = mast.FK_Table2
)t
GROUP BY MasterField1,Table1Field1,Seq

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-11 : 08:21:58

I am considering the duplicate entry is not a typo "Y Beta First" in the proposed data for MasterTable
Then to have desired result, replace the row_number line (in Visakh proposed query) with following

select row_number() over (partition by mast.MasterField1, child1.Table1Field1,Table2Field1 order by (select 1)) AS Seq, mast.MasterField1, child1.Table1Field1,

[Edit]: he has already updated it for you :)
Cheers
MIK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 08:27:25

quote:
Originally posted by MIK_2008

I am considering the duplicate entry is not a typo "Y Beta First" in the proposed data for MasterTable
Then to have desired result, replace the row_number line (in Visakh proposed query) with following

select row_number() over (partition by mast.MasterField1, child1.Table1Field1,Table2Field1 order by (select 1)) AS Seq, mast.MasterField1, child1.Table1Field1,

[Edit]: he has already updated it for you :)

Cheers
MIK

Thats the problem with not having a SQL box nearby for testing...An effect of moving from coding level long back

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Zifter
Yak Posting Veteran

51 Posts

Posted - 2013-06-11 : 08:46:01

quote:

Originally posted by visakh16

one more attempt


select MasterField1,Table1Field1,
MAX(CASE WHEN Table2Field1= 'First' THEN Table2Field1 END) AS First,
MAX(CASE WHEN Table2Field1= 'Second' THEN Table2Field1 END) AS Second
from
(
select row_number() over (partition by mast.MasterField1, child1.Table1Field1,child2.Table2Field1  order by (select 1)) AS Seq, mast.MasterField1, child1.Table1Field1 ,
child2.Table2Field1
from #MasterTable as mast
left join #ChildTable1 as child1
  on child1.ID = mast.FK_Table1
left join #ChildTable2 as child2
  on child2.ID = mast.FK_Table2
)t
GROUP BY MasterField1,Table1Field1,Seq

This is it!
Thank you all for all the help!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 12:29:41

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources