CODEWITHSUNDEEP
sqlteradata
IDDQD
IDDQD

Reputation: 35

Several rows with info to a single row

Hi I need to merge 2 tables with information about customers. Table 2 tells us if we have customer information about email, address and phonenumber, but the data is structured so each customer has 3 rows. Is there a way to merge these two tables so that I only get one row per customer but with all the contact information?

Table 1:

id  customerID  ... ...

1   11 

2   99

and Table 2:

id  customerID  Channel Y_N

1   11          Email    Y

2   11          Address  Y

3   11          Phone    N

4   99          Email    N

5   99          Address  Y

6   99          Phone    N

My code is this

TABLE 1
left join(
    select customerID, 
    case when Y_N='Y' and Channel='Email' then 1 else 0 end as Email
    FROM table2 
    where Channel='Email')a
    on table1.customerID=a.customerID
Left join(
    select customerID, 
    case when Y_N='Y' and Channel='Address' then 1 else 0 end as Address
    FROM table2
    where Channel='Address') b
    on table1.customerID=b.customerID
Left join(
    select customerID, 
    case when Y_N='Y' and Channel='Phone' then 1 else 0 end as Phone
    FROM table2
    where Channel='SMS') c
    on  table1.customerID=c.customerID

which actually does the job, but if I have to do it again in the future is there then a smarter way?

Thank you

Upvotes: 1

Views: 49

Answers (3)

dnoeth
dnoeth

Reputation: 60462

You can replace those three joins with a single usong conditional aggregation (=pivot):

TABLE1
left join(
    select customerID, 
       max(case when Y_N='Y' and Channel='Email' then 1 else 0 end) as Email
       max(case when Y_N='Y' and Channel='Address' then 1 else 0 end) as Address
       max(case when Y_N='Y' and Channel='Phone' then 1 else 0 end) as Phone
    FROM table2 
    GROUP BY 1) a
    on table1.customerID=a.customerID

This might be more efficient, check Explain...

Upvotes: 1

fqhv
fqhv

Reputation: 1201

If you are expecting new channels in the future you could use this to dynamically add channels as columns:

DECLARE @SearchList     varchar(MAX)
DECLARE @sql            varchar(MAX)

select @SearchList = COALESCE(@SearchList, '') + ', [' + cast(Channel as VARCHAR(100)) + ']' 
from (select distinct channel from #table2) a

set @sql = 'select CustomerID, ' + RIGHT(@SearchList, LEN(@SearchList)-1) +'
from
(select CustomerID, Channel, Y_N
    from #table2) as t
pivot
(
    max(Y_N)
    for Channel in (' + RIGHT(@SearchList, LEN(@SearchList)-1) + ')
) as pvt'

exec(@sql)

Upvotes: 0

OscarJ
OscarJ

Reputation: 413

Well, if it works, why change it? But if you have to, maybe something like:

SELECT
    c.*,
    IF(e.Y_N='Y',1,0) AS Email,
    IF(a.Y_N='Y',1,0) AS Address,
    IF(p.Y_N='Y',1,0) AS Phone
FROM table1 AS c
   LEFT JOIN table2 AS e ON(c.customerID=e.customerID AND Channel='Email')
   LEFT JOIN table2 AS a ON(c.customerID=a.customerID AND Channel='Address')
   LEFT JOIN table2 AS p ON(c.customerID=p.customerID AND Channel='Phone')

Also, I can't really see a good reason to have three rows per customer in table2. If at all possible, you'd be better off changing it to

customerID|Email|Address|Phone
    11    |  1  |   1   |  0
    99    |  0  |   1   |  0

Such that you can just do

SELECT * FROM table1 AS a LEFT JOIN table2 AS b ON a.customerID=b.customerID

Upvotes: 0

Related Questions