May 15, 2019 at 12:49 pm
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
you haven't given a chance to help you and not provided
btw, nice article to read :
May 15, 2019 at 1:05 pm
This is an example of the data.
May 15, 2019 at 1:13 pm
And my desired output.
May 15, 2019 at 1:17 pm
please, show the output of the following:
select @@version
May 15, 2019 at 1:32 pm
select @@version = Microsoft SQL server 2012 - 11.0.2100.60(x64)
May 15, 2019 at 2:02 pm
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
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
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
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
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
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