Multiple rows on one row - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple rows on one row

Author

Topic

leazo
Starting Member

2 Posts

Posted - 2008-07-24 : 15:21:08

Hi,

I have three tables:

Orders Products Orders_Products
ID ID ID_Order
.... Description ID_Products

I need to make a report with the next information:

Order, Product1, Product2, Product3

Each order may have up to three products
I need to show Only a row for each order with its associated products (each associated product as a field)

Please, someone could help me to do a query for that?
(SQL 2005)

thanks to everyone

Leazo

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-07-24 : 19:57:16

See http://technet.microsoft.com/en-us/library/ms177410.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 00:46:21

you can use this also:-

SELECT t.OrderDesc,
MAX(CASE WHEN RowNo=1 THEN t.Description ELSE NULL END) AS Product1,
MAX(CASE WHEN RowNo=2 THEN t.Description ELSE NULL END) AS Product2,
MAX(CASE WHEN RowNo=3 THEN t.Description ELSE NULL END) AS Product3
FROM
(
SELECT o.*,p.*,
row_number() over(partition by o.OrderDesc order by p.ID) AS RowNo
FROM Orders o
INNER JOIN Orders_Products op
ON op.ID_Order=o.ID
INNER JOIN Products p
ON op.ID_Products=p.ID)t
GROUP BY t.OrderDesc

leazo
Starting Member

2 Posts

Posted - 2008-07-25 : 10:47:25

Thanks a lot, people!

Cheers

Leazo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources