Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2010
    Posts
    3

    Unanswered: condense and concatenate

    Need to be able to condense records that are similar except the last field where I want that concatenated:

    Example:
    Field1 Field2 Field3 Field4
    table wood tall oak
    chair wood tall pine
    table wood tall cherry
    table wood short oak
    table metal tall black
    table wood short walnut

    I basically want 4 results:
    chair wood tall pine
    table metal tall black
    table wood short oak;walnut
    table wood tall oak;cherry

    Unable to figure out how to make the select statement in SQL Server 2005 to get the results I need. A procedure or function would work as well. I just need to be able to call in MSAccess(2010).
    Last edited by dpf107; 11-22-10 at 09:47.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You'll find lots of code for this on the web. There's three ways I know of doing this.
    1. UDF
    2. Using FOR XML
    3. Recursive CTE

    2 & 3 are the best methods. 1 will work in SQL 2000

    sql server csv colun - Google Search
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2010
    Posts
    3

    condencse and concatenate

    The examples I found on the web in all honesty confuse me. I don't get how to convert my code to this since there are many columns. So what I'll do is post my actual sql code. There is one column that is creating duplicate entries - SH.[External Document No_] AS [Sales PO No]

    Maybe this will help or confuse even more

    SELECT DISTINCT LRH.[Pickup Location Code]
    , LRL.[Planned Appointment Date] AS [Planned Appt Date]
    , CONVERT(varchar, LRL.[Planned Appointment Time], 108) AS [Planned Appt Time]
    , LRH.No_ AS SC#, LRH.Description AS [Customer Name]
    , LCL.Comment, LRH.[Driver Name]
    , LRH.[Planned Pickup Date]
    , CONVERT(varchar, LRH.[Planned Pickup Time], 108) AS [Planned Pickup Time]
    , LRL.[Appointment No_]
    , SSH.No_ AS [Sales Shipment No]
    , SSH.[External Document No_]
    , LRL.[Source Doc_ Ref_ No_] AS [Sales Order No]
    , SH.[External Document No_] AS [Sales PO No] <<<<this is the field to concatenate.
    , LRH.[Shipping Agent Code]
    , LRH.[Shipment Method Code]
    FROM [Sales Header] AS SH RIGHT OUTER JOIN
    [Logistics Run Line] AS LRL ON SH.No_ = LRL.[Source Doc_ Ref_ No_] RIGHT OUTER JOIN
    [Logistics Run Header] AS LRH LEFT OUTER JOIN
    [Sales Shipment Header] AS SSH ON LRH.No_ = SSH.[Shipment Consolidation No_] LEFT OUTER JOIN
    [Logistics Comment Line] AS LCL ON LRH.No_ = LCL.No_ ON LRL.[Run No_] = LRH.No_
    WHERE(LRH.[Shipping Agent Code] = 'here')
    AND (LRH.[Shipment Method Code] = 'here')
    AND (LRL.[Planned Appointment Date] BETWEEN CONVERT(varchar(11), GETDATE()+ 0) AND CONVERT(varchar(11), GETDATE() + 3))
    ORDER BY [Planned Appt Date]
    , [Planned Appt Time]
    , LRH.[Pickup Location Code]
    , SC#

  4. #4
    Join Date
    Nov 2010
    Posts
    3
    OK here was my first attempt which didn't work:
    No error, still gave duplicate lines. Double checked results and this field is the only one that is different from all the other fields.

    Maybe it is the where statement?? I just run the "select...xml path" without the where and it concatenates everything in that field. Hummm..

    SELECT DISTINCT LRH.[Pickup Location Code]
    , LRL.[Planned Appointment Date] AS [Planned Appt Date]
    , CONVERT(varchar, LRL.[Planned Appointment Time], 108) AS [Planned Appt Time]
    , LRH.No_ AS SC#, LRH.Description AS [Customer Name]
    , LCL.Comment, LRH.[Driver Name]
    , LRH.[Planned Pickup Date]
    , CONVERT(varchar, LRH.[Planned Pickup Time], 108) AS [Planned Pickup Time]
    , LRL.[Appointment No_]
    , SSH.No_ AS [Sales Shipment No]
    , SSH.[External Document No_]
    , stuff( (
    select ',' + SH.[External Document No_]
    from [Sales Header] AS SH
    where SH.No_ = LRL.[Source Doc_ Ref_ No_]
    for xml path('')
    ),1,1,'') as [Sales PO No List]

    , LRH.[Shipping Agent Code]
    , LRH.[Shipment Method Code]
    FROM [Logistics Run Line] AS LRL RIGHT OUTER JOIN
    [Logistics Run Header] AS LRH LEFT OUTER JOIN
    [Sales Shipment Header] AS SSH ON LRH.No_ = SSH.[Shipment Consolidation No_] LEFT OUTER JOIN
    [Logistics Comment Line] AS LCL ON LRH.No_ = LCL.No_ ON LRL.[Run No_] = LRH.No_
    WHERE(LRH.[Shipping Agent Code] = 'here')
    AND (LRH.[Shipment Method Code] = 'here')
    AND (LRL.[Planned Appointment Date] BETWEEN CONVERT(varchar(11), GETDATE()+ 0) AND CONVERT(varchar(11), GETDATE() + 3))
    ORDER BY [Planned Appt Date]
    , [Planned Appt Time]
    , LRH.[Pickup Location Code]
    , SC#
    Last edited by dpf107; 11-22-10 at 10:54.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •