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)
 Pivot? Rank Over? ...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Zifter
Starting Member

44 Posts

Posted - 06/11/2013 :  07:09:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 06/11/2013 :  07:23:28  Show Profile  Reply with Quote
--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
Go to Top of Page

Zifter
Starting Member

44 Posts

Posted - 06/11/2013 :  07:33:31  Show Profile  Reply with Quote
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...

Edited by - Zifter on 06/11/2013 07:41:49
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/11/2013 :  07:42:49  Show Profile  Reply with Quote

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 


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Zifter
Starting Member

44 Posts

Posted - 06/11/2013 :  08:00:25  Show Profile  Reply with Quote
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?)

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/11/2013 :  08:07:17  Show Profile  Reply with Quote
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
Go to Top of Page

Zifter
Starting Member

44 Posts

Posted - 06/11/2013 :  08:16:12  Show Profile  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/11/2013 :  08:19:33  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 06/11/2013 :  08:21:58  Show Profile  Reply with Quote
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

Edited by - MIK_2008 on 06/11/2013 08:24:19
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/11/2013 :  08:27:25  Show Profile  Reply with Quote
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
Go to Top of Page

Zifter
Starting Member

44 Posts

Posted - 06/11/2013 :  08:46:01  Show Profile  Reply with Quote
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!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/11/2013 :  12:29:41  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.19 seconds. Powered By: Snitz Forums 2000