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.
Author |
Topic |
nguthier
Starting Member
1 Post |
Posted - 2010-02-01 : 16:54:04
|
The child table will have multiple records per parent. I need to build a query that will display the child data as multiple columns in a single row. Any ideas?Order--------OrderNumber OrderDate1 2009-01-012 2009-03-31OrderItems----------------OrderNumber, Item Quantity1 Widgets 51 Gadgets 41 Nuts 32 Bolts 10I need the output to show asOrderNumber OrderDate ItemOne ItemOneQuantity ItemTwo ItemTwoQuantity ItemThree ItemThreeQuantity1 2009-01-01 Widgets 5 Gadgets 4 Nuts 3 |
|
Lumbago
Norsk Yak Master
3271 Posts |
|
raky
Aged Yak Warrior
767 Posts |
Posted - 2010-02-02 : 04:31:59
|
Hi,Try This DECLARE @order TABLE ( ordernumber VARCHAR(255), orderdate DATETIME)INSERT INTO @order SELECT 1, '2009-01-01'UNION ALLSELECT 2, '2009-03-31'DECLARE @orderitems TABLE ( ordernumber VARCHAR(255), item VARCHAR(100), quantity INT)INSERT INTO @orderitemsSELECT 1, 'Widgets', 5 UNION ALLSELECT 1, 'Gadgets', 4 UNION ALLSELECT 1, 'Nuts', 3 UNION ALLSELECT 2, 'Bolts', 10SELECT p.OrderNumber, p.OrderDate, MAX([Item 1]) AS ItemOne, MAX([Itemquantity 1]) AS ItemOneQuantity ,MAX([Item 2]) AS ItemTwo, MAX([Itemquantity 2]) AS ItemTwoQuantity,MAX([Item 3]) AS ItemThree, MAX([Itemquantity 3]) AS ItemThreeQuantityfrom ( SELECT ROW_NUMBER() OVER ( PARTITION BY o.ordernumber ORDER BY o.ordernumber ) as row , 'Item ' + CAST(ROW_NUMBER() OVER ( PARTITION BY o.ordernumber ORDER BY o.ordernumber ) AS VARCHAR(255)) AS itemquantity,o.ordernumber, o.orderdate, ot.item from @order as o INNER JOIN @orderitems as ot on ot.ordernumber = o.ordernumber UNION ALLSELECT ROW_NUMBER() OVER ( PARTITION BY o.ordernumber ORDER BY o.ordernumber ) as row, 'Itemquantity ' + CAST(ROW_NUMBER() OVER ( PARTITION BY o.ordernumber ORDER BY o.ordernumber ) AS VARCHAR(255)) AS itemquantity ,o.ordernumber, o.orderdate, CAST(ot.quantity AS VARCHAR(255)) AS itemquantityFROM @order AS o INNER JOIN @orderitems AS ot ON ot.ordernumber = o.ordernumber ) AS sPIVOT ( MAX(item) FOR itemquantity in ([Item 1],[Itemquantity 1],[Item 2],[Itemquantity 2],[Item 3],[Itemquantity 3]))AS p GROUP BY p.ordernumber, p.orderdate |
|
|
raky
Aged Yak Warrior
767 Posts |
Posted - 2010-02-02 : 05:06:34
|
Hi Try This in Dynamic[CODE]CREATE TABLE #order ( ordernumber VARCHAR(255), orderdate DATETIME)INSERT INTO #order SELECT 1, '2009-01-01'UNION ALLSELECT 2, '2009-03-31'CREATE TABLE #orderitems ( ordernumber VARCHAR(255), item VARCHAR(100), quantity INT)INSERT INTO #orderitemsSELECT 1, 'Widgets', 5 UNION ALLSELECT 1, 'Gadgets', 4 UNION ALLSELECT 1, 'Nuts', 3 UNION ALLSELECT 2, 'Bolts', 10declare @str varchar( max) select @str = ''select @str = @str + ',[Item' + CASE WHEN row = 1 THEN 'One' WHEN row = 2 THEN 'Two' WHEN row = 3 THEN 'Three' ELSE cast( row as varchar(255)) END + '] , ' + '[Item' + CASE WHEN row = 1 THEN 'One' WHEN row = 2 THEN 'Two' WHEN row = 3 THEN 'Three' ELSE cast( row as varchar(255)) END + 'quantity] ' FROM ( SELECT DISTINCT row_number() OVER ( PARTITION BY ordernumber ORDER BY ordernumber ) AS row FROM #orderitems ) as sselect @str = stuff( @str, 1, 1, '')--SELECT @strdeclare @stg1 VARCHAR(MAX)SELECT @stg1 = ''SELECT @stg1 = 'SELECT * FROM ( SELECT ''Item'' + CASE WHEN CAST(ROW_NUMBER() OVER ( PARTITION BY o.ordernumber ORDER BY o.ordernumber ) AS VARCHAR(255)) = 1 THEN ''One'' WHEN CAST(ROW_NUMBER() OVER ( PARTITION BY o.ordernumber ORDER BY o.ordernumber ) AS VARCHAR(255)) = 2 THEN ''Two'' WHEN CAST(ROW_NUMBER() OVER ( PARTITION BY o.ordernumber ORDER BY o.ordernumber ) AS VARCHAR(255)) = 3 THEN ''Three'' else CAST(ROW_NUMBER() OVER ( PARTITION BY o.ordernumber ORDER BY o.ordernumber ) AS VARCHAR(255)) end AS itemquantity,o.OrderNumber, o.OrderDate, ot.item from #order as o INNER JOIN #orderitems as ot on ot.ordernumber = o.ordernumber UNION ALLSELECT''Item'' + CASE WHEN CAST(ROW_NUMBER() OVER ( PARTITION BY o.ordernumber ORDER BY o.ordernumber ) AS VARCHAR(255)) = 1 THEN ''One'' WHEN CAST(ROW_NUMBER() OVER ( PARTITION BY o.ordernumber ORDER BY o.ordernumber ) AS VARCHAR(255)) = 2 THEN ''Two'' WHEN CAST(ROW_NUMBER() OVER ( PARTITION BY o.ordernumber ORDER BY o.ordernumber ) AS VARCHAR(255)) = 3 THEN ''Three'' else CAST(ROW_NUMBER() OVER ( PARTITION BY o.ordernumber ORDER BY o.ordernumber ) AS VARCHAR(255)) end +''quantity'' AS itemquantity ,o.ordernumber, o.orderdate, CAST(ot.quantity AS VARCHAR(255)) AS itemFROM #order AS o INNER JOIN #orderitems AS ot ON ot.ordernumber = o.ordernumber ) AS SPIVOT ( MAX(S.item) FOR Itemquantity IN (' + @str + ' )) AS P 'exec ( @stg1) DROP TABLE #orderDROP TABLE #orderitems[/CODE] |
|
|
|
|
|
|
|