0

Is there any way in SQL server to get the child records to be displayed as the fields of the select clause of master table?

Suppose a master table Sales and a child table called Purchaseditems.

The SaleId is referenced in the Purchaseitems table.

So for a sale, there are lot of purchase items. If so, how I can write a query to select each sales with its purchased items in the select clause?

This is a kind of dynamic column selection from child table. Is this possible in SQL server?

Kara
  • 6,115
  • 16
  • 50
  • 57
Sunil Sha
  • 65
  • 1
  • 9

1 Answers1

0

Not sure if i'm getting the wrong idea here, but isn't this just a simple join:

select s.saleid, p.* 
from sales s, purchaseditems p
where s.salesid = p.salesid;
Joe
  • 236
  • 2
  • 9
  • Hi, thanks for the reply. But this is not what i expected. I should get the child column values as the dynamic column of the master table select clause. Say, select s.saleno, (select p.itemname from purchaseditems p) from Sales S join PurchasedItems p on s.saleid = p.saleid...something like this...any help ??? – Sunil Sha Aug 14 '12 at 08:31
  • Is this more like what you're trying to do... http://stackoverflow.com/questions/3368942/grouped-string-aggregation-listagg-for-sql-server or this http://stackoverflow.com/questions/2046037/sql-server-can-i-comma-delimit-multiple-rows-into-one-column – Joe Aug 14 '12 at 09:03
  • Thanks for the reply. But this will not solve the issue. But solved it using pivot table. Thanks for the hints. – Sunil Sha Aug 16 '12 at 08:56