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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 HELP Displaying multiple child records in same row

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 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

3271 Posts

Posted - 2010-02-02 : 02:32:43
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 - 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 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 - 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 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

[/CODE]
Go to Top of Page
   

- Advertisement -