I have a table with 12 columns. I want to create a group by distinct on the data and concatenate one of the columns data. I think I can use a union but not sure. Here is the code:
select distinct MID, IID, LOB, PROD, Market, Accountability, Audience,
Owners, HM, FQ, LM, LY, Vendor, Q1, Q2, Q3, Q4, EP, CP, NCP, CNBR, type
into test
select 'Mailer', 10 union all
select 'Letter', 10 union all
select 'IVR', 10 union all
select 'Card', 10 union all
select 'Mailer', 20 union all
select 'Letter', 20 union all
select 'IVR', 20 union all
select 'Card'. 20 union all
from original
group by MID, IID, LOB, PROD, Market, Accountability, Audience,
Owners, HM, FQ, LM, LY, Vendor, Q1, Q2, Q3, Q4, EP, CP, NCP, CNBR;
Just to give an example of data have:
MID IID LOB PROD Market Accountability Audience Owners HM FQ LM LY Vendor type
1 10 HI PP CA HMM Prov Sarah BC N Jan 15 DCS Mailer
1 10 HI PP CA HMM Prov Sarah BC N Jan 15 DCS Letter
1 10 HI PP CA HMM Prov Sarah BC N Jan 15 DCS IVR
1 10 HI PP CA HMM Prov Sarah BC N Jan 15 DCS Card
The other columns are identical as well. The variance is with type. What I want is to have it look like this:
MID IID LOB PROD Market Accountability Audience Owners HM FQ LM LY Vendor Type
1 10 HI PP CA HMM Prov Sarah BC N Jan 15 DCS Mailer+Letter+IVR+Card