June 1, 2010 at 8:04 am
Hi All,
I am having trouble putting this query together, any tips would be helpful.
The table structure looks like this
CustomerNum PostDate OfferAccepted OfferName
1000 5/1/10 Y Test1
1000 5/1/10 Y Test2
1000 5/1/10 N Test3
1001 5/1/10 N Test1
1001 5/1/10 N Test2
1001 5/1/10 Y Test3
I want to flatten the table by each CustomerNum e.g.
CustomerNum PostDate OfferAccepted OfferName1 OfferName2 OfferName3
1000 5/1/10 Y Test1 Test2 Test3
1000 5/1/10 Y Test3
Reason for that is i have an older process that updates a dest. table that looks like the above flattened table. I am adding a new source and trying to do a UNION of the two tables so i need the newer one flattened. The only important thing above is I only keep the CustomerNumbers is they have atleast 1 OfferAccepted=Y and no more than 3.
Sorry the table cell alignment is a bit off.
Thanks in advance!
June 1, 2010 at 8:20 am
There are two great articles by MVP Jeff Moden on this site that show you exactly how to do what you need:
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 3, 2010 at 1:03 pm
Thanks Paul but if you look at what i am trying to do here, Pivoting is the easy part. How to relate the records is what i am having difficulty with and pivot doesn't help there.
June 3, 2010 at 1:50 pm
In the future it helps to put together table definitions and insert statements so people can more easily help you:
create table #table2
(
CustomerNum int,
PostDate datetime,
OfferAccepted char(1),
OfferName1 varchar(10),
OfferName2 varchar(10),
OfferName3 varchar(10)
)
insert into #table2
select 2001, '5/1/10', 'Y', 'Test1', 'Test2', 'Test3' union
select 2002, '5/1/10', 'Y', 'Test3', null, null union
select 2003, '5/2/10', 'N', 'Test2', 'Test3', null
If I understand you right, you just want to put your second table (above) in the same format as your first table so you can do a union. This is just an UNPIVOT:
select CustomerNum,
PostDate,
OfferAccepted,
OfferName
from #table2
unpivot
(
OfferName for value in
(OfferName1, OfferName2, OfferName3)
) as unpvt;
drop table #table2
June 3, 2010 at 2:05 pm
berry, no your assumption is wrong
table1 doesnt have 3 fixed offer names..it can have N offers..
my source table is table1
my dest table is table2
its complicated because i only start with a customernumber if it has atleast 1 offer with offeraccepted=y and i need to the place the top 2 offers next to it, all from the same table so max 3 offers.
THEN i need to somehow pivot that into
CUSTOMERNUM .... OFFER1 OFFER2 OFFER3
June 3, 2010 at 2:20 pm
Put together table definitions (as I have done above) for all of the relevant source tables. Then write out the script to insert a meaningful amount of data into said tables (as I have done above.) Then give an example of what you want to have returned from your query based on the structures and data you've provided. A bonus addition would be to show what you've tried to resolve your own problem.
Do all of this and I would be glad to help you out. Otherwise, it's kind of a waste of time to guess about what you're trying to do.
June 3, 2010 at 2:26 pm
BIMind (6/3/2010)
berry, no your assumption is wrongtable1 doesnt have 3 fixed offer names..it can have N offers..
my source table is table1
my dest table is table2
its complicated because i only start with a customernumber if it has atleast 1 offer with offeraccepted=y and i need to the place the top 2 offers next to it, all from the same table so max 3 offers.
THEN i need to somehow pivot that into
CUSTOMERNUM .... OFFER1 OFFER2 OFFER3
See? We're guessing. Please see the article at the first link in my signature line below for a better way to post your questions. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 7, 2010 at 8:19 am
Sorry guys!
Here goes:
create table #table2
(
CustomerNum int,
PostDate datetime,
OfferAccepted char(1),
OfferName varchar(10),
)
insert into #table2
select 2001, '5/1/10', 'Y', 'Test1' union
select 2001, '5/1/10', 'N', 'Test2' UNION
select 2001, '5/1/10', 'N', 'Test3' UNION
select 2001, '5/1/10', 'N', 'Test4' UNION
select 2002, '5/2/10', 'Y', 'Test3' union
select 2003, '5/2/10', 'N', 'Test1' UNION
select 2003, '5/2/10', 'N', 'Test2' UNION
select 2003, '5/2/10', 'N', 'Test4' UNION
select 2003, '5/2/10', 'N', 'Test5'
Desired output is
CustomerNum PostDate Offer1 Offer2 Offer3
2001 5/1/10 Test1 Test2 Test3
2002 5/2/10 Test3
Explanation: Pick top 3 offers by CustomerNum. CustomerNum must have atleast 1 offer with OfferAccepted = Y. Display Top 3 offers as columns (only 1 record per CustomerNum)
June 7, 2010 at 10:45 am
You say the question isn't about how to pivot (and Paul linked an article for you about that anyway), so this is just a matter of limiting your records as you say:
with cteTemp(CustomerNum, PostDate, Offer, RowNum)
as
(
select t.CustomerNum,
t.PostDate,
t.OfferName,
ROW_NUMBER() OVER (PARTITION BY t.CustomerNum ORDER BY t.OfferName ASC)
from #table2 t
where exists
(
select sq.OfferName
from #table2 sq
where sq.CustomerNum = t.CustomerNum
and sq.OfferAccepted = 'Y'
)
)
select CustomerNum, PostDate, Offer, RowNum
from cteTemp
where RowNum <= 3
order by CustomerNum, RowNum
You're going to have issues though if your OfferName is in a char/varchar datatype as it is in this example. SQL will evaluate 'Test2' to be greater than 'Test10' so it is a poor basis to evaluate the order.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply