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?
