Can I use UNPIVOT

Question

Post reply

Can I use UNPIVOT

terry999

SSCertifiable

Points: 5033

November 18, 2020 at 2:37 pm

Go to Answer

#3809220

Hi I have a table which I would like to turn columns into rows.

create table Customer
(
CIDint  not null,
WholeSalerbit not null,
ReSalerbit not null,
Retailbit not null
)

insert into Customer
values (1, 0,0,1),
 (2, 1,1,1),
 (3, 1,0,0)

I would like to get this output

CIDCustType
1Retail
2Retail
2WholeSaler
2ReSaler
3WholeSaler

is there a way to do this with UNPIVOT or CASE statements etc. other than

 select CID, 'WholeSaler' as CustType
 FROM Customer WHERE WholeSaler =1
 UNION ALL
  select CID, 'ReSaler'
 FROM Customer WHERE ReSaler =1
  UNION ALL
  select CID, 'Retail'
 FROM Customer WHERE Retail =1
 order by CID

Steve Collins

SSCrazy

Points: 2068

November 18, 2020 at 3:57 pm

Answer

#3809269

drop table if exists #Customer
go
create table #Customer(
  CIDint  not null,
  WholeSaler        bit not null,
  ReSalerbit not null,
  Retailbit not null);

insert into #Customer values
(1,0,0,1),
(2,1,1,1),
(3,1,0,0);

select unpvt.CID, unpvt.CustType
from #Customer c
     cross apply (values (CID, 'Retail', Retail),
                         (CID, 'WholeSaler', WholeSaler),
                         (CID, 'ReSaler', ReSaler)) unpvt(CID, CustType, bit_val)
where unpvt.bit_val=1;

CIDCustType
1Retail
2Retail
2WholeSaler
2ReSaler
3WholeSaler

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 5 posts - 1 through 4 (of 4 total)

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

frederico_fonseca SSCoach Points: 16011 More actions · Answer 1

a simple outer apply would do.

single pass on the table and it is clearer than Pivot/unpivot (at least for me)

select CID
     , t.CustType
from Customer c
outer apply (select 'Resaler' as CustType
             where ReSaler = 1
             union all
             select 'WholeSaler' as CustType
             where WholeSaler = 1
             union all
             select 'Retail' as CustType
             where Retail = 1
             ) t

terry999 SSCertifiable Points: 5033 More actions · Answer 2

Thanks, not only do I struggle with UNPIVOT.

I also cannot figure out apply. I get it when passing parameters in e.g. apply yo every value.

Is this the same as

select unpvt.CID, unpvt.CustType,bit_val
from customer c
     cross apply (
 SELECT CID, 'Retail' as CustType, Retail as bit_val
 union all
 SELECT CID, 'WholeSaler', WholeSaler
 union all
 SELECT CID, 'ReSaler', ReSaler
) unpvt
where unpvt.bit_val=1;

Steve Collins SSCrazy Points: 2068 More actions · Answer 3

When I run each query and include the actual execution plan the results are identical. Both do one table scan and a constant scan on the results. So yes they appear to be the same.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können