June 21, 2004 at 12:26 pm
I am trying to write a query (or queries) to return the unique combinations of items purchased by a person. The data looks something like this...
Person Item # Item Description
------------------------------------------------------------
John 123044 Gas Trimmer
John 123105 Elite Vacuum
John 194122 Shop-Sweep Indoor/Outdoor Vac
Jim 123044 Gas Trimmer
Jim 123105 Elite Vacuum
Jim 194122 Shop-Sweep Indoor/Outdoor Vac
Jim 123099 Folding Chairs
Should return something like this...
Person Item #1 Item Description 1 Item #2 Item Description 2
-----------------------------------------------------------------------------------------------------------
John 123044 Gas Trimmer 123105 Elite Vacuum
John 123044 Gas Trimmer 194122 Shop-Sweep Indoor/Outdoor Vac
John 123105 Elite Vacuum 194122 Shop-Sweep Indoor/Outdoor Vac
John 123044 Gas Trimmer 123105 Elite Vacuum
John 123044 Gas Trimmer 194122 Shop-Sweep Indoor/Outdoor Vac
John 123044 Gas Trimmer 123099 Folding Chairs
John 123105 Elite Vacuum 123122 Shop-Sweep Indoor/Outdoor Vac
John 123105 Elite Vacuum 123099 Folding Chairs
John 123122 Shop-Sweep Indoor/Outdoor Vac 123099 Folding Chairs
I was getting somewhere using a set of nested cursors, but i was wondering if there was a more efficient and/or elegant solution.
Any ideas?
Thanks,
Anton
June 21, 2004 at 4:50 pm
How about:
select distinct s1.Person,
[Item #1]=s1.[Item #] ,
[Item Description 1]=s1.[Item Description] ,
[Item #2]=s2.[Item #] ,
[Item Description 2]=s2.[Item Description]
from sales s1
join sales s2 on s1.person = s2.person and s1.[Item #] < s2.[Item #]
order by 1,2,4
-- need DISTINCT in case they've bought an item more than once
Cheers,
- Mark
June 21, 2004 at 8:59 pm
select a.*, b.itemnum, b.itemdesc from #t a, #t b
where a.person = b.person
and a.itemnum > b.itemnum
order by 1,2,4
person itemnum itemdesc itemnum itemdesc
----------------------------------------------------------------------
Jim 123099 Folding Chairs 123044 Gas Trimmer
Jim 123105 Elite Vacuum 123044 Gas Trimmer
Jim 123105 Elite Vacuum 123099 Folding Chairs
Jim 194122 Shop-Sweep Indoor/Outdoor Vac 123044 Gas Trimmer
Jim 194122 Shop-Sweep Indoor/Outdoor Vac 123099 Folding Chairs
Jim 194122 Shop-Sweep Indoor/Outdoor Vac 123105 Elite Vacuum
John 123105 Elite Vacuum 123044 Gas Trimmer
John 194122 Shop-Sweep Indoor/Outdoor Vac 123044 Gas Trimmer
John 194122 Shop-Sweep Indoor/Outdoor Vac 123105 Elite Vacuum
[font="Courier New"]ZenDada[/font]
June 22, 2004 at 12:55 am
What you want is a PIVOT.
Unless the #items possible to buy is known in advance, this is best to do on the client side.
It gets very ugly in Transact-SQL, although it's doable.
/Kenneth
June 22, 2004 at 8:12 am
Thanks for all of the help in getting me over my brain fart. The queries work fantasic.
Anton
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply