How to combine records in same table (Flatten a table) ?

Question

Post reply

How to combine records in same table (Flatten a table) ?

BIMind

SSChasing Mays

Points: 628
More actions
June 1, 2010 at 8:04 am

#223212

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!

Viewing 9 posts - 1 through 8 (of 8 total)

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

Paul White SSC Guru Points: 150468 More actions · Answer 1

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
All articles available on SQL.kiwi
@SQL_Kiwi

BIMind SSChasing Mays Points: 628 More actions · Answer 2

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.

Ben Teraberry SSChampion Points: 11845 More actions · Answer 3

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

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

BIMind SSChasing Mays Points: 628 More actions · Answer 4

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

Ben Teraberry SSChampion Points: 11845 More actions · Answer 5

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.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

BIMind (6/3/2010)
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

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BIMind SSChasing Mays Points: 628 More actions · Answer 7

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)

Ben Teraberry SSChampion Points: 11845 More actions · Answer 8

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.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]