locked
Aggregate Function to Concatenate Columns Data into a single Row RRS feed

  • Question

  • 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.               

    Thursday, August 9, 2007 2:13 AM

All replies

  • 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]

     

     

     

     

    Thursday, August 9, 2007 2:28 AM
  • 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?

    Thursday, August 9, 2007 4:12 AM
  •  

    Post your query.. I didn't understand the dynamic data / on the fly.. You can achive this without function.
    Thursday, August 9, 2007 5:01 AM
  • 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.

    Thursday, August 9, 2007 10:16 AM
  • 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 by  W.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

     

     

     

     

     

    Thursday, August 9, 2007 12:22 PM
  • 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?

    Friday, August 10, 2007 1:38 AM