How to associate unequal number of rows from two separate tables

Question

How to associate unequal number of rows from two separate tables

BIMind

SSChasing Mays

Points: 628
More actions
June 24, 2010 at 11:19 am

#220351

Hi
I am having trouble putting the following query together
I have a customer's before and after plans in two separate tables. i am trying to associate them together and see which ones were kept/dropped/added. A full outer join isnt working for me, any ideas?
DECLARE @before_table table (CustNum varchar(10), Event_Date datetime, Before_plan varchar(10))
DECLARE @after_table table (CustNum varchar(10), Event_Date datetime, Before_plan varchar(10))
INSERT INTO
@before_table
SELECT '1000','2010-06-01','AAB'
UNION
SELECT '1000','2010-06-01','AAC'
UNION
SELECT '1000','2010-06-01','AAD'
UNION
SELECT '1000','2010-06-02','AAD'
UNION
SELECT '1001','2010-06-02','AAD'
INSERT INTO
@after_table
SELECT '1000','2010-06-01','AAB'
UNION
SELECT '1000','2010-06-01','AAD'
UNION
SELECT '1000','2010-06-02','AAE'
UNION
SELECT '1001','2010-06-02','AAF'
select *
FROM @before_table
select *
FROM @after_table
The output i want needs to look like this
CustNum EVENT_Date B_Plan1 A_Plan1 B_plan2 A_plan2 B_plan3 A_plan3.....
1000 2010-06-01 AAB AAB AAC NULL AAD AAD
1001 2010-06-02 AAD NULL NULL AAF

Viewing 3 posts - 1 through 2 (of 2 total)

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

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

If you want to crosstab the data into X columns where X is dynamically determined, you will need to use dynamic SQL. Jeff Moden has a nice article about how to do this here[/url].

└> 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 2

thanks bb...someone else had posted this which works quite well

; with

combined_table

as

(

selectCustNum, Event_Date, plan_code, type,

rowno = dense_rank() over (partition by CustNum, Event_Date order by plan_code)

from

(

select CustNum, Event_Date, plan_code = Before_plan, type = 'B'

FROM @before_table

union all

select CustNum, Event_Date, plan_code = Before_plan, type = 'A'

FROM @after_table

) p

)

selectCustNum, Event_Date,

B_Plan1 = max(case when type = 'B' and rowno = 1 then plan_code end),

A_Plan1 = max(case when type = 'A' and rowno = 1 then plan_code end),

B_Plan2 = max(case when type = 'B' and rowno = 2 then plan_code end),

A_Plan2 = max(case when type = 'A' and rowno = 2 then plan_code end),

B_Plan3 = max(case when type = 'B' and rowno = 3 then plan_code end),

A_Plan3 = max(case when type = 'A' and rowno = 3 then plan_code end)

fromcombined_table

group by CustNum, Event_Date