April 29, 2012 at 7:24 am
Hi,
I have a table like,
Invoice No Vendor
1 vendor 1
2 Vendor 1
And i want it to in a third table like this,
Vendor Inovice Issues
Vendor 1 1,2
How can i do this. Thank you.
April 29, 2012 at 9:51 am
Look up how to use PIVOT tables
***The first step is always the hardest *******
April 29, 2012 at 10:50 am
Your end result demands more data, "issues". Let's use this example:
create table Invoice
(
InvoiceId int identity(1, 1) not null primary key clustered,
Vendor varchar(128) not null -- I will argue this should reference a Vendor table with a foreign key relationship
)
go
create table Issue
(
Vendor varchar(128) not null,
InvoiceId int not null foreign key references Invoice(InvoiceId),
IssueNumber int -- just for domain logic's sake
)
go
Again, in the Issue table I'd recommend having a foreign key relationship to a separate Vendor table. That way it's not stored as a vendor's name, but a lookup in case the vendor data ever changed (and like most environments, it probably would). Mold that Issue table to your business requirements.
To get the result set you're looking for, you'd do a simple aggregration:
select
Vendor,
InvoiceId,
count(*) as IssuesCount
from Issue
group by Vendor, InvoiceId
April 29, 2012 at 12:23 pm
farrukh635 (4/29/2012)
Hi,I have a table like,
Invoice No Vendor
1 vendor 1
2 Vendor 1
And i want it to in a third table like this,
Vendor Inovice Issues
Vendor 1 1,2
How can i do this. Thank you.
First, since you're new, take a look at the article at the first link in my signature line below. It'll help you get coded/tested answers instead of nearly rhetorical answers much more quickly.
Second, it looks like you're trying to a sorted comma separated list of invoice numbers for each vendor. Be advised that's normally a pretty bad idea to do in any database because the data isn't exactly normalized and will bring much pain in the future when you try to use the data.
With that in mind and the fact that there will always be an unknown number of invoices, it would appear that the PIVOT answer previously suggested just won't fit the bill. I'm pretty sure the COUNT suggestion isn't what you want or need, either.
Here's a solution that creates the CSV of all invoice numbers for each vendor, just like you what you posted. If I've misinterpreted what you needed, please post back.
--=============================================================================
-- Create and populate a test table. This is NOT a part of the solution.
--=============================================================================
--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#ATable','U') IS NOT NULL DROP TABLE #ATable;
--===== Create and populate the test table on the fly.
SELECT TOP 10000
InvoiceNumber = IDENTITY(INT,1,1),
Vendor = 'Vendor '
+ RIGHT('000' + CAST(ABS(CHECKSUM(NEWID())) % 20 + 1 AS VARCHAR(10)),3)
INTO #ATable
FROM sys.all_columns ac1,
sys.all_columns ac2
;
--=============================================================================
-- Now, solve the problem using some XML concatenation to create the CSV.
--=============================================================================
SELECT Vendor,
(
STUFF((
SELECT ',' + CAST(t2.InvoiceNumber AS VARCHAR(MAX))
FROM #ATable t2
WHERE t2.Vendor = t1.Vendor
ORDER BY t2.InvoiceNumber
FOR XML PATH('')
),1,1,'')
)
FROM #ATable t1
GROUP BY t1.Vendor
ORDER BY t1.Vendor
--Jeff Moden
Change is inevitable... Change for the better is not.
April 30, 2012 at 1:03 am
Hi Jeff,
The solution you provided is what i need 🙂 and, thank you for the suggestion.I'll be checking the links you provided.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply