Aggregate Function to Concatenate Columns Data into a single Row

Hi all,

I have a scenario which I am not able to figure out how to do it better for quite some time.

Assume I have a few rows of data :

RunningID Date WOid

1234 1/23/2007 23

1236 1/24/2007 23

1239 1/2/2007 24

1222 1/4/2007 23

1321 2/4/2007 22

My objective is to merge all RunningID into a single cell when WOid is the same (this will most probably use a "group by" to get the different WOid out). Maybe some aggregate function that can do it as:

select ReturnConca(RunningID, "#") as RunningID_str, max(Date) as MaxDate, max(WOid) as WO from tableXXX

group by WOid

Results:

RunningID_str MaxDate WO

1234#1236#1222 1/24/2007 23

1239 1/2/2007 24

1321 2/4/2007 22

Any advise would be much appreciated.

If you use SQL server 2005,

Code Snippet

Create Table #data (

[RunningID] int ,

[Date] datetime ,

[WOid] int

);

Insert Into #data Values('1234','1/23/2007','23');

Insert Into #data Values('1236','1/24/2007','23');

Insert Into #data Values('1239','1/2/2007','24');

Insert Into #data Values('1222','1/4/2007','23');

Insert Into #data Values('1321','2/4/2007','22');

Select

[RunningIDs],

Max([Date]) [Date],

[WoId]

From

(

select

Substring((Select '#' + cast([RunningID] as varchar) as [text()] from #data sub

where sub.[Woid] = main.[Woid] for xml path('')

),2,8000) as [RunningIDs],

[Date],

[WoId]

from

#data main

) as data

Group By

[RunningIDs],[WoId]

Order By

[RunningIDs]

|||

Thanks for your code.

However, this must be done on the fly and there are many similar rows in a single selection and how do we encapsulate the above code into a function. If not, how do we insert the dynamic data into the temp table on the fly?

|||

Post your query.. I didn't understand the dynamic data / on the fly.. You can achive this without function.|||

Here is my query:

SELECT P.running as PRunning, P.[date], P.refWO, P.addWO,
W.id,W.running as WRunning,W.[date] as WO_Date
FROM WorkOrder W
RIGHT JOIN PurchaseOrder P ON (P.refWO=W.id)
where not W.id is null
UNION ALL

SELECT P.running as PRunning, P.[date], P.refWO, P.addWO,
W.id,W.running as WRunning, W.[date] as WO_Date
FROM WorkOrder W
RIGHT JOIN PurchaseOrder P ON (P.addWO like ('%#' + cast(W.id as varchar) + ':%'))
where not W.id is null
order by W.id, W.[date], W.running

The PRunning and P.date will have a few rows to one P.refWO. his might be occuring a few times over the result.

|||

May be something like this,

Code Snippet

SELECT P.running as PRunning, P.[date], P.refWO, P.addWO,

W.id,W.running as WRunning,W.[date] as WO_Date into #temp

FROM WorkOrder W

RIGHT JOIN PurchaseOrder P ON (P.refWO=W.id)

where not W.id is null

UNION ALL

SELECT P.running as PRunning, P.[date], P.refWO, P.addWO,

W.id,W.running as WRunning, W.[date] as WO_Date

FROM WorkOrder W

RIGHT JOIN PurchaseOrder P ON (P.addWO like ('%#' + cast(W.id as varchar) + ':%'))

where not W.id is null

order byW.id, W.[date], W.running

Select

PRunning

,Max([date])

,refWO

,addWO

,WRunning

,max(WO_Date)

From

(

Select

(Select '#' + PRunning as [text()] from #temp sub where sub.id = main.id For xml path('')) as PRunning

,[date]

,(Select '#' + refWO as [text()] from #temp sub where sub.id = main.id For xml path('')) as refWO

,(Select '#' + addWO as [text()] from #temp sub where sub.id = main.id For xml path('')) as addWO

,(Select '#' + WRunning as [text()] from #temp sub where sub.id = main.id For xml path('')) as WRunning

,WO_Date

from

#temp

) as Data

Group By

PRunning

,refWO

,addWO

,WRunning

|||

Hi I am using SQL 2000 and I suppose i need some minor tweating to the code. When i run the code, It reported invalid for "For XML Path('')'. So i took those out.

Another issue is where does the alias "main" referring to?