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
 General SQL Server Forums
 New to SQL Server Programming
 HELP Displaying multiple child records in same row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nguthier
Starting Member

USA
1 Posts

Posted - 02/01/2010 :  16:54:04  Show Profile  Reply with Quote
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 OrderDate
1 2009-01-01
2 2009-03-31

OrderItems
----------------
OrderNumber, Item Quantity
1 Widgets 5
1 Gadgets 4
1 Nuts 3
2 Bolts 10

I need the output to show as
OrderNumber OrderDate ItemOne ItemOneQuantity ItemTwo ItemTwoQuantity ItemThree ItemThreeQuantity
1 2009-01-01 Widgets 5 Gadgets 4 Nuts 3

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 02/02/2010 :  02:32:43  Show Profile  Reply with Quote
What you're looking for is dynamic pivoting... check out madhis blog on the topic:

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein
Go to Top of Page

raky
Aged Yak Warrior

767 Posts

Posted - 02/02/2010 :  04:31:59  Show Profile  Reply with Quote

Hi,
Try This

DECLARE @order TABLE 
( ordernumber VARCHAR(255), orderdate DATETIME)
INSERT INTO @order 
SELECT 1, '2009-01-01'
UNION ALL
SELECT 2, '2009-03-31'

DECLARE @orderitems TABLE 
( ordernumber VARCHAR(255), item VARCHAR(100), quantity INT)
INSERT INTO @orderitems
SELECT 1, 'Widgets', 5 UNION ALL
SELECT 1, 'Gadgets', 4 UNION ALL
SELECT 1, 'Nuts', 3 UNION ALL
SELECT 2, 'Bolts', 10

SELECT 
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 ItemThreeQuantity
from ( 
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 ALL
SELECT 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 itemquantity
FROM @order AS o 
INNER JOIN 
	@orderitems AS ot ON ot.ordernumber = o.ordernumber ) AS s
PIVOT ( 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
Go to Top of Page

raky
Aged Yak Warrior

767 Posts

Posted - 02/02/2010 :  05:06:34  Show Profile  Reply with Quote
Hi Try This in Dynamic



CREATE TABLE #order  
( ordernumber VARCHAR(255), orderdate DATETIME)
INSERT INTO #order 
SELECT 1, '2009-01-01'
UNION ALL
SELECT 2, '2009-03-31'

CREATE TABLE  #orderitems  
( ordernumber VARCHAR(255), item VARCHAR(100), quantity INT)
INSERT INTO #orderitems
SELECT 1, 'Widgets', 5 UNION ALL
SELECT 1, 'Gadgets', 4 UNION ALL
SELECT 1, 'Nuts', 3 UNION ALL
SELECT 2, 'Bolts', 10

declare @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 s



select @str = stuff( @str, 1, 1, '')
--SELECT @str
declare @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 ALL
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 +''quantity''   AS itemquantity ,
o.ordernumber,  
o.orderdate, 
CAST(ot.quantity AS VARCHAR(255)) AS item
FROM #order AS o 
INNER JOIN 
	#orderitems AS ot ON ot.ordernumber = o.ordernumber ) AS S
PIVOT ( MAX(S.item) FOR Itemquantity IN (' + @str + ' )) AS P '

exec ( @stg1) 


DROP TABLE #order

DROP TABLE #orderitems


Edited by - raky on 02/02/2010 05:42:26
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.12 seconds. Powered By: Snitz Forums 2000