InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 09:39:41 GMT20http://www.sqlservercentral.com/Forums/Topic1383942-391-1.aspxYou can try this. It looks like the results you're after but why you'd want such bizarre results eludes me (enlightnment requested :-)).[code="sql"]SELECT PntKey=ISNULL(PntKey, Cd2PntKey) ,PntQty=CASE WHEN n1 = 1 THEN PntQty END ,FromDate1, ToDate1 ,FromDate2=Cd2FromDate, ToDate2=Cd2ToDateFROM ( 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) aFULL 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[/code]Wed, 14 Nov 2012 02:13:36 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1383942-391-1.aspxI came up with this code:[code="sql"]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 workgroup by PntKey, RowNumber, CASE WHEN RowNumber = 1 THEN Qty ELSE 0 END[/code]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-311 2 0 2012-04-01 2012-05-01 NULL NULL2 1 200 2012-01-15 2012-05-15 2012-01-01 2012-02-012 2 0 NULL NULL 2012-02-15 2012-05-153 1 300 2012-05-01 2012-07-01 2012-09-01 2012-10-01Warning: Null value is eliminated by an aggregate or other SET operation.(5 row(s) affected)Tue, 13 Nov 2012 16:41:42 GMTgcressehttp://www.sqlservercentral.com/Forums/Topic1383942-391-1.aspxThanks 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-311 [b]100[/b] 2012-04-01 2012-05-01 [b]2011-12-01 2011-12-31[/b]2 200 2012-01-15 2012-05-15 2012-01-01 2012-02-012 [b]200[/b] [b] 2012-01-15 2012-05-15 [/b]2012-02-15 2012-05-153 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.Tue, 13 Nov 2012 09:59:41 GMTgcressehttp://www.sqlservercentral.com/Forums/Topic1383942-391-1.aspxTo further support my above proposal, attached please find the relevant SQL execution plans comparing your SELECT and my proposalTue, 13 Nov 2012 07:47:33 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic1383942-391-1.aspxInstead of using union all etc, rather consider using joins:[code="sql"]select PntKey, #Parent.PntQty Qty, #Child1.Cd1FromDate FromDate1, #Child1.Cd1ToDate ToDate1, #Child2.Cd2FromDate FromDate2, #Child2.Cd2ToDate ToDate2FROM #ParentLEFT JOIN #Child1 ON #Child1.Cd1PntKey = #Parent.PntKeyLEFT JOIN #Child2 ON #Child2.Cd2PntKey = #Parent.PntKey[/code]Tue, 13 Nov 2012 07:34:47 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic1383942-391-1.aspxI 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.[code="sql"]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, 100UNION ALL SELECT 2, 200UNION ALL SELECT 3, 300INSERT 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, ToDate2FROM ( 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 WORKORDER BY PntKey, Qty desc[/code]The final SELECT statement returns results like this:PntKey Qty FromDate1 ToDate1 FromDate2 ToDate21 100 NULL NULL NULL NULL1 NULL 2012-01-01 2012-02-01 NULL NULL1 NULL 2012-04-01 2012-05-01 NULL NULL1 NULL NULL NULL 2011-12-01 2011-12-312 200 NULL NULL NULL NULL2 NULL NULL NULL 2012-01-01 2012-02-012 NULL NULL NULL 2012-02-15 2012-05-152 NULL 2012-01-15 2012-05-15 NULL NULL3 300 NULL NULL NULL NULL3 NULL 2012-05-01 2012-07-01 NULL NULL3 NULL NULL NULL 2012-09-01 2012-10-01But what I'd really like is something like this:PntKey Qty FromDate1 ToDate1 FromDate2 ToDate21 100 1/1/2012 2/1/2012 12/1/2011 12/31/20111 NULL 4/1/2012 5/1/2012 NULL NULL2 200 1/15/2012 5/15/2012 1/1/2012 2/1/20122 NULL NULL NULL 2/15/2012 5/15/20123 300 5/1/2012 7/1/2012 9/1/2012 10/1/2012Is it possible to get the data back in a flatter way?Thanks.Mon, 12 Nov 2012 22:22:21 GMTgcresse