Concatenating column values for each uniqueid

I have a table like this.

OrderId

QueueId

Template

Subject

RecdDtim

SentDtim

1

1

TEM1

Sub1

11/3/2005 8:10:10

11/3/2005 9:00:15

1

2

TEM3

Sub2

11/3/2005 10:25:45

11/3/2005 10:40:35

2

3

TEM1

Sub2

11/3/2005 11:45:35

11/3/2005 12:00:00

1

4

TEM2

Sub3

11/4/2005 10:25:00

11/4/2005 10/30:00

2

5

TEM3

Sub3

11/4/2005 11:56:00

11/4/2005 12:00:00

I want to concatenate Template, Subject and SentDtim column values for each order and display results like this:

OrderId

Email

1

TEM1,Sub1,11/3/2005 | TEM3,Sub2,11/3/2005|TEM2,Sub3,11/4/2005

2

TEM1,Sub2,11/3/2005 | TEM3, Sub3, 11/4/2005

Please help me how to write the sql query to insert the results in the above format into a temporary table so that I can join that temp table with another tableon orderid column.

There was a thread few days back titled "Concatenate field based on unique id. ". But you are trying to build a report using SQL and it is not going to look pretty. You are better off sending the results as is to the client and format accordingly. It is much more easier to format the results easily.|||I want to insert the results into a temp table so that I can join that temp table with another table.|||I have to agree with UC that this sounds like a hack.

Anyway, if you are using SQL Server 2005, you can use basically the same solution as I provided fpr the question in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=124926&SiteID=1:

create table why (OrderId int, QueueId int, Template nvarchar(10),
Subject nvarchar(10), RecdDtim datetime, SentDtim datetime)
go

insert into why
select 1, 1, N'TEM1', N'Sub1', '11/3/2005 8:10:10', '11/3/2005 9:00:15'
union
select 1,2, N'TEM3', N'Sub2', '11/3/2005 10:25:45', '11/3/2005 10:40:35'
union
select 2,3, N'TEM1', N'Sub2', '11/3/2005 11:45:35', '11/3/2005 12:00:00'
union
select 1, 4, N'TEM2', N'Sub3', '11/4/2005 10:25:00', '11/4/2005 10:30:00'
union
select 2, 5, N'TEM3', N'Sub3', '11/4/2005 11:56:00', '11/4/2005 12:00:00'

select o.OrderId,
substring(
(select '|', substring(
(select ',', i.Template as "text()", ',', i.Subject as "text()",
',', CONVERT(nvarchar(10), RecdDtim, 1)
from why ii
where ii.QueueId = i.QueueId and ii.OrderId = i.OrderId
for xml path('')),2, 8000)
from why i
where i.OrderId = o.OrderId
for xml path('')),2,8000)
from why o
group by o.OrderId

Best regards
Michael

|||I want to know the solution in SQL Server 2000. Thanks for the help.|||If you are joining the results with another temporary table then why do you want to denormalize the data? You could use the method that I described in the other thread. But it seems unnecessary to me. Maybe it will help if you post some sample schema, data and expected results we can suggest a different solution.|||Order details are stored in Orders table. But the emails sent to customers in respect of orders are stored in a different table (EmailQueue). For one order, multiple emails may be sent. I want to display order information in a datagrid as well as to export the data to excel format. In the last column of this grid/excel sheet, I want to display template, subject and date sent of each email sent in respect of that order. I hope I have made clear my requirement.|||What you are trying to do is perfectly reasonable with tsql. Here is code that uses a cursor to loop through each row in the table.

Joyce

CREATE TABLE #T (OrderId int not null, EmailString varchar(1000) not null)

DECLARE @.OrderId int, @.EmailString varchar(1000)

,@.OldOrderId int, @.FirstString varchar(1000)

SET @.OldOrderId = -999

SET @.OrderId = 0

SET @.EmailString = ''

SET @.FirstString = ''

DECLARE RowCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY

FOR

SELECT

OrderId

,i.Template + ','+ i.Subject + ',' + CONVERT(varchar(10), RecdDtim, 1)

FROM why i

ORDER BY OrderId, Queueid

OPEN RowCursor

FETCH NEXT FROM RowCursor INTO @.OrderId, @.FirstString

WHILE (@.@.FETCH_STATUS = 0)

BEGIN

-- select @.oldorderid, @.orderid -- some debug code

IF @.OldOrderId = -999 SET @.OldOrderId = @.OrderId -- for first time through

IF @.OldOrderId <> @.OrderId

BEGIN

INSERT INTO #t (OrderId, EmailString) VALUES (@.OldOrderId, @.EmailString)

SET @.OldOrderId = @.OrderId

SET @.EmailString= ''

END

-- for the next QueueId within the same OrderId, concatenate a vertical bar between strings

SET @.EmailString = CASE WHEN @.EmailString = ''

THEN @.FirstString

ELSE @.EmailString + '|' + @.FirstString

END

FETCH NEXT FROM RowCursor INTO @.OrderId, @.FirstString

END

-- The last row was fetched so insert the OrderId

INSERT INTO #t (OrderId, EmailString) VALUES (@.OldOrderId, @.EmailString)

CLOSE RowCursor

DEALLOCATE RowCursor

-- See what is in there

select * from #t order by orderid