Need Help creating an import SQL function

  • ccameron 64337

    SSC Veteran

    Points: 253

    I have data that has Vendor name, Vendor number, Invoice number, Invoice date and Check number. I need to concatenate the Invoice number and Invoice date every time Check numbers are repeated. The data would then need to be:

    Vendor name, Vendor number, Invoice1 Invoice2 Invoice3, Invoice date1 Invoice date2, Invoice date3, Check number

    This is the desired output.

    </p>

  • Andrey

    SSChasing Mays

    Points: 617

    you haven't given a chance to help you and not provided

    • sample data
    • desired output

    btw, nice article to read :

    https://www.mssqltips.com/sqlservertip/6030/how-to-build-an-efficient-request-process-for-sql-server-dbas/

  • ccameron 64337

    SSC Veteran

    Points: 253

    This is an example of the data.

  • ccameron 64337

    SSC Veteran

    Points: 253

    And my desired output.

  • Andrey

    SSChasing Mays

    Points: 617

    please, show the output of the following:

    select @@version
  • ccameron 64337

    SSC Veteran

    Points: 253

    select @@version = Microsoft SQL server 2012 - 11.0.2100.60(x64)

  • Andrey

    SSChasing Mays

    Points: 617

    see attached

    • This reply was modified 3 months, 1 week ago by  Andrey. Reason: attachment corrected
    Attachments:
    You must be logged in to view attached files.
  • ccameron 64337

    SSC Veteran

    Points: 253

    In some cases I have 20 or more invoices and invoice dates on one check so I would have to add more

    ,ltrim((select ' '+d4.[Invoice No]

    commands.

    I also see an advantage to upgrade to SQL 2017.

    Thank you very much for showing me the 2 methods. Very much appreciated! I apologize for having all the data needed at first.

  • Andrey

    SSChasing Mays

    Points: 617

    ok, glad it helped

     

    here is the code:

    with data ([Vendor Name],[Vendor No],[Invoice No],[INVOICE DATE],[Check No]) as 
    (
    select 'Company123', '11445','11112','20180502',123456
    union all select 'Company123', '11445', '11113', '20180601',123456
    union all select 'Company123', '11445', '11116', '20180608',123456
    union all select 'New123', '22111', '12554', '20180502',234567
    union all select 'Old456', '231211', '55246', '20180601',345678
    union all select 'Other678', '100000', '11222665', '20180701',678932
    )
    select
    d1.[Vendor Name]
    ,d1.[Vendor No]
    ,ltrim((select ' '+d2.[Invoice No] as [data()] from data d2 where d2.[Vendor Name]=d1.[Vendor Name] and d2.[Vendor No]=d1.[Vendor No] and d2.[Check No]=d1.[Check No] order by d2.[Invoice No] for xml path(''))) [Invoice No]
    ,ltrim((select ' '+d3.[INVOICE DATE] as [data()] from data d3 where d3.[Vendor Name]=d1.[Vendor Name] and d3.[Vendor No]=d1.[Vendor No] and d3.[Check No]=d1.[Check No] order by d3.[Invoice No] for xml path(''))) [Invoice Date]

    /* for SQL2017+
    ,string_agg([Invoice No],' ') within group (order by [Invoice No]) [Invoice No]
    ,string_agg([INVOICE DATE],' ') within group (order by [Invoice No]) [Invoice Date]
    */
    ,d1.[Check No]
    from data d1
    group by
    d1.[Vendor Name]
    ,d1.[Vendor No]
    ,d1.[Check No]
  • ccameron 64337

    SSC Veteran

    Points: 253

    Forgive my ignorance here.

    How would I do the select and union all select as a variable?

    select 'Company123', '11445','11112','20180502',123456

    union all select 'Company123', '11445', '11113', '20180601',123456

    union all select 'Company123', '11445', '11116', '20180608',123456

    The table I am dealing with is 70,000 rows with hundreds of vendors. I only posted an example.

  • Andrey

    SSChasing Mays

    Points: 617

    ccameron 64337 wrote:

    Forgive my ignorance here. How would I do the select and union all select as a variable?  The table I am dealing with is 70,000 rows with hundreds of vendors. I only posted an example.

    variable is not needed here

    just replace 'YOURTABLE' with the name of your table

    also, there can be issues with real data types you have in your table

     

    example:

     

    select 
    d1.[Vendor Name]
    ,d1.[Vendor No]
    ,ltrim((select ' '+d2.[Invoice No] as [data()] from YOURTABLE d2 where d2.[Vendor Name]=d1.[Vendor Name] and d2.[Vendor No]=d1.[Vendor No] and d2.[Check No]=d1.[Check No] order by d2.[Invoice No] for xml path(''))) [Invoice No]
    ,ltrim((select ' '+d3.[INVOICE DATE] as [data()] from YOURTABLE d3 where d3.[Vendor Name]=d1.[Vendor Name] and d3.[Vendor No]=d1.[Vendor No] and d3.[Check No]=d1.[Check No] order by d3.[Invoice No] for xml path(''))) [Invoice Date]

    /* for SQL2017+
    ,string_agg([Invoice No],' ') within group (order by [Invoice No]) [Invoice No]
    ,string_agg([INVOICE DATE],' ') within group (order by [Invoice No]) [Invoice Date]
    */
    ,d1.[Check No]
    from YOURTABLE d1
    group by
    d1.[Vendor Name]
    ,d1.[Vendor No]
    ,d1.[Check No]
  • ccameron 64337

    SSC Veteran

    Points: 253

    For some reason it's giving me a syntax error with d1.[Vendor Name] after the select command.

  • ccameron 64337

    SSC Veteran

    Points: 253

    My bad! It's working now. Thank you so much!

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply