Post reply

Need Help creating an import SQL function

  • May 15, 2019 at 12:49 pm

    #3642321

    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>

  • May 15, 2019 at 12:56 pm

    #3642323

    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/

  • May 15, 2019 at 1:05 pm

    #3642338

    This is an example of the data.

  • May 15, 2019 at 1:13 pm

    #3642343

    And my desired output.

  • May 15, 2019 at 1:17 pm

    #3642350

    please, show the output of the following:

    select @@version

  • May 15, 2019 at 1:32 pm

    #3642362

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

  • May 15, 2019 at 1:40 pm

    #3642364

    see attached

    • This reply was modified 4 years, 11 months ago by  Andrey. Reason: attachment corrected

    Attachments:
    You must be logged in to view attached files.

  • May 15, 2019 at 2:02 pm

    #3642377

    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.

  • May 15, 2019 at 2:07 pm

    #3642388

    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]

  • May 15, 2019 at 3:18 pm

    #3642422

    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.

  • May 15, 2019 at 3:49 pm

    #3643934

    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]

  • May 15, 2019 at 4:21 pm

    #3643949

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

  • May 15, 2019 at 5:33 pm

    #3643985

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

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

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