Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to flatten results from parent/children tables in view Expand / Collapse
Author
Message
Posted Monday, November 12, 2012 10:22 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 12:56 PM
Points: 77, Visits: 392
I have a parent table and 2 children tables that store date ranges. I'm trying to create a view to support a transaction report and I'm having trouble getting the rows flattened so I don't have to have so many rows returned.

CREATE TABLE Parent (
PntKey int,
PntQty int)

CREATE TABLE Child1 (
Cd1Key int,
Cd1PntKey int,
Cd1FromDate date,
Cd1ToDate date)

CREATE TABLE Child2 (
Cd2Key int,
Cd2PntKey int,
Cd2FromDate date,
Cd2ToDate date)

INSERT INTO Parent (PntKey, PntQty)
SELECT 1, 100
UNION ALL SELECT 2, 200
UNION ALL SELECT 3, 300

INSERT INTO Child1 (Cd1Key, Cd1PntKey, Cd1FromDate, Cd1ToDate)
SELECT 1,1,'1-1-2012','2-1-2012'
UNION ALL SELECT 2,1,'4-1-2012','5-1-2012'
UNION ALL SELECT 3,2,'1-15-2012','5-15-2012'
UNION ALL SELECT 4,3,'5-1-2012','7-1-2012'

INSERT INTO Child2 (Cd2Key, Cd2PntKey, Cd2FromDate, Cd2ToDate)
SELECT 1,1,'12-1-2011','12-31-2011'
UNION ALL SELECT 2,2,'1-1-2012','2-1-2012'
UNION ALL SELECT 3,2,'2-15-2012','5-15-2012'
UNION ALL SELECT 4,3,'9-1-2012','10-1-2012'

SELECT PntKey,
Qty,
FromDate1,
ToDate1,
FromDate2,
ToDate2
FROM (
SELECT PntKey PntKey,
PntQty Qty,
NULL FromDate1,
NULL ToDate1,
NULL FromDate2,
NULL ToDate2
FROM Parent
UNION ALL
SELECT Cd1PntKey PntKey,
NULL Qty,
Cd1FromDate FromDate1,
Cd1ToDate ToDate1,
NULL FromDate2,
NULL ToDate2
FROM Child1
UNION ALL
SELECT Cd2PntKey PntKey,
NULL Qty,
NULL FromDate1,
NULL ToDate1,
Cd2FromDate FromDate2,
Cd2ToDate ToDate2
FROM Child2
) AS WORK

ORDER BY PntKey, Qty desc

The final SELECT statement returns results like this:
PntKey Qty FromDate1 ToDate1 FromDate2 ToDate2
1 100 NULL NULL NULL NULL
1 NULL 2012-01-01 2012-02-01 NULL NULL
1 NULL 2012-04-01 2012-05-01 NULL NULL
1 NULL NULL NULL 2011-12-01 2011-12-31
2 200 NULL NULL NULL NULL
2 NULL NULL NULL 2012-01-01 2012-02-01
2 NULL NULL NULL 2012-02-15 2012-05-15
2 NULL 2012-01-15 2012-05-15 NULL NULL
3 300 NULL NULL NULL NULL
3 NULL 2012-05-01 2012-07-01 NULL NULL
3 NULL NULL NULL 2012-09-01 2012-10-01


But what I'd really like is something like this:
PntKey Qty FromDate1 ToDate1 FromDate2 ToDate2
1 100 1/1/2012 2/1/2012 12/1/2011 12/31/2011
1 NULL 4/1/2012 5/1/2012 NULL NULL
2 200 1/15/2012 5/15/2012 1/1/2012 2/1/2012
2 NULL NULL NULL 2/15/2012 5/15/2012
3 300 5/1/2012 7/1/2012 9/1/2012 10/1/2012

Is it possible to get the data back in a flatter way?

Thanks.
Post #1383942
Posted Tuesday, November 13, 2012 7:34 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:51 AM
Points: 4,158, Visits: 5,556
Instead of using union all etc, rather consider using joins:
select PntKey,
#Parent.PntQty Qty,
#Child1.Cd1FromDate FromDate1,
#Child1.Cd1ToDate ToDate1,
#Child2.Cd2FromDate FromDate2,
#Child2.Cd2ToDate ToDate2
FROM #Parent
LEFT JOIN #Child1 ON #Child1.Cd1PntKey = #Parent.PntKey
LEFT JOIN #Child2 ON #Child2.Cd2PntKey = #Parent.PntKey



____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1384096
Posted Tuesday, November 13, 2012 7:47 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:51 AM
Points: 4,158, Visits: 5,556
To further support my above proposal, attached please find the relevant SQL execution plans comparing your SELECT and my proposal

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”


  Post Attachments 
ParentChild.sqlplan (0 views, 28.38 KB)
Post #1384105
Posted Tuesday, November 13, 2012 9:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 12:56 PM
Points: 77, Visits: 392
Thanks for responding. I actually did try using joins as my first choice, but the results were incorrect:

PntKey Qty FromDate1 ToDate1 FromDate2 ToDate2
----------- ----------- ---------- ---------- ---------- ----------
1 100 2012-01-01 2012-02-01 2011-12-01 2011-12-31
1 100 2012-04-01 2012-05-01 2011-12-01 2011-12-31
2 200 2012-01-15 2012-05-15 2012-01-01 2012-02-01
2 200 2012-01-15 2012-05-15 2012-02-15 2012-05-15
3 300 2012-05-01 2012-07-01 2012-09-01 2012-10-01

(5 row(s) affected)

For PntKey 1 the FromDate2 and ToDate2 are duplicated and for PntKey 2 the FromDate1 and ToDate1 are duplicated, but I only want them to appear once. Also, the Qty gets duplicated, which will overstate it. It seems like it shouldn't be this much of a challenge but I've been struggling to come up with something that works.
Post #1384176
Posted Tuesday, November 13, 2012 4:41 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 12:56 PM
Points: 77, Visits: 392
I came up with this code:
select	PntKey,
RowNumber,
CASE
WHEN RowNumber = 1 THEN Qty
ELSE 0
END Qty,
min(FromDate1),
max(ToDate1),
min(FromDate2),
max(ToDate2)
from

(
select PntKey,
ROW_NUMBER() OVER(PARTITION BY PntKey ORDER BY PntKey) AS 'RowNumber',
Parent.PntQty Qty,
Child1.Cd1FromDate FromDate1,
Child1.Cd1ToDate ToDate1,
null FromDate2,
null ToDate2
FROM Parent
LEFT JOIN Child1 ON Child1.Cd1PntKey = Parent.PntKey

union
select PntKey,
ROW_NUMBER() OVER(PARTITION BY PntKey ORDER BY PntKey) AS 'RowNumber',
Parent.PntQty Qty,
null FromDate1,
null ToDate1,
Child2.Cd2FromDate FromDate2,
Child2.Cd2ToDate ToDate2
FROM Parent
LEFT JOIN Child2 ON Child2.Cd2PntKey = Parent.PntKey
) as work
group by PntKey,
RowNumber,
CASE
WHEN RowNumber = 1 THEN Qty
ELSE 0
END

And I get results like this:
PntKey RowNumber Qty FromDate1 ToDate1 FromDate2 ToDate2
----------- -------------------- ----------- ---------- ---------- ---------- ----------
1 1 100 2012-01-01 2012-02-01 2011-12-01 2011-12-31
1 2 0 2012-04-01 2012-05-01 NULL NULL
2 1 200 2012-01-15 2012-05-15 2012-01-01 2012-02-01
2 2 0 NULL NULL 2012-02-15 2012-05-15
3 1 300 2012-05-01 2012-07-01 2012-09-01 2012-10-01
Warning: Null value is eliminated by an aggregate or other SET operation.

(5 row(s) affected)

Post #1384343
Posted Wednesday, November 14, 2012 2:13 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:53 PM
Points: 3,438, Visits: 5,390
You can try this. It looks like the results you're after but why you'd want such bizarre results eludes me (enlightnment requested ).

SELECT PntKey=ISNULL(PntKey, Cd2PntKey)
,PntQty=CASE WHEN n1 = 1 THEN PntQty END
,FromDate1, ToDate1
,FromDate2=Cd2FromDate, ToDate2=Cd2ToDate
FROM (
SELECT p.PntKey
,p.PntQty
,FromDate1=Cd1FromDate
,ToDate1=Cd1ToDate
,n1=ROW_NUMBER() OVER (PARTITION BY PntKey ORDER BY Cd1Key)
FROM #Parent p
RIGHT JOIN #Child1 c1 ON p.PntKey = c1.Cd1PntKey) a
FULL JOIN (
SELECT Cd2Key, Cd2PntKey, Cd2FromDate, Cd2ToDate
,n2=ROW_NUMBER() OVER (PARTITION BY Cd2PntKey ORDER BY Cd2Key)
FROM #Child2) c2
ON a.PntKey = c2.Cd2PntKey AND a.n1 = c2.n2
ORDER BY PntKey





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1384473
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse