asked on

TSQL - Select multiple rows into single row. (Master / detail tables)

I am trying to put together a query that summarises a month worth of items in a detail table and I'm struggling a bit. Basically what I want to have at the end is:

      Item 1 - 15 - £10
      Item 2 - 1 - £12
      Covers deliveries 1232, 1234, 502. Dates 01/01/2000, 01/02/2000, 01/02/2000

The item rows are easy, but I'm not sure how to put together that final row. Tables being worked with are something like this:

      Summary Table:
      ID
      Total
      Date

      Detail Table:
      ID
      SummaryID
      DeliveryNumber
      DeliveryDate

I can do a simple select to return all detail rows required (SELECT [DeliveryNumber], [DeliveryDate] FROM [DetailTable] WHERE [SummaryID] = @SummaryID) but I need to get this into a single row and formatted as closely as I can to the sample above.

Any solutions?

ASKER CERTIFIED SOLUTION

Chrisedebo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

AntonyDN

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Psychotext

ASKER

Thanks both... will have a look and post back.

Psychotext

ASKER

Both are excellent, thanks. Have given points to both as Chrisedebo was first but AntonyDN tailored the example specifically to my needs.

Thanks again.

pdms123456

Here's a little known technique that can be useful when flattening out row values from a column

Declare @v VARCHAR (1000)

SELECT @v = 'Covers Deliveries: '

select @v = @v + DeliveryNumber + ', ' FROM [Detail Table] WHERE SummaryID = etc