group by

[anorthcote]

Hi,

I'm wanting to do a query with a group by statement to group some text field together and can't think of a way to do it. Have a look at my sample data below

orderid Detail
26146 Attendnace on 27/11/06 to repair fire alarm
26147 Key holding fee 01/07/06 - 30/06/07
26148 Hardware Maintenance 13/10/06 - 12/10/06
26148 Software Maintenance 13/10/06 - 12/10/06

I want to concatenate the detail field to get just one entry per orderid as follows

orderid Detail
26146 Attendnace on 27/11/06 to repair fire alarm
26147 Key holding fee 01/07/06 - 30/06/07
26148 Hardware Maintenance 13/10/06 - 12/10/06, Software Maintenance 13/10/06 - 12/10/06

I hope this makes sense and is possible in SQL.

Thanks for your help.

Andrew

 

[r937]

see faq183-2146

r937.com | rudy.ca

 

[anorthcote]

Hi,

I have tried the code you suggested

Create table #tmp(
keyval int,
CombinedLine varchar(8000)
)

Insert #tmp
Select Distinct orderid,''
From orderline

Declare @ln int, @mln int
Select @ln=1, @mln=Max(id)
From orderline

While @ln<=@mln
Begin
Update #tmp
Set CombinedLine=CombinedLine+b.detail+' - '
From #tmp a
Join orderline b
ON a.keyval=b.orderid
Where b.id=@ln

Select @ln=@ln+1
End

Select * from #tmp
Drop table #tmp
go

and I get the following error

Server: Msg 457, Level 16, State 1, Line 16
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

Any ideas what that means????