CODEWITHSUNDEEP
sql-serversql-server-2008unpivot
ubertastic
ubertastic

Reputation: 183

SQL Server Unpivot on Pair Columns

I apologize for the poorly worded title, I was not sure how else to describe this question.

I have a table in SQL Server 2008 which looks like this, wonky schema aside:

enter image description here

I would like to process a query given a "name" to produce these results:

enter image description here

I have looked at a number of pivot table examples and have come up with inconclusive results. I have not had much practice in advanced, complex queries.

Edit: Here is an SQL Fiddle link: http://sqlfiddle.com/#!3/28f93/1

Upvotes: 2

Views: 1217

Answers (4)

Girish
Girish

Reputation: 11

--Oracle version:
create table tst_unpivot
(
  name varchar2(32) null,
  descriptor1a varchar(32) null,
  descriptor1b  varchar(32) null,
  descriptor2a  varchar(32) null,
  descriptor2b  varchar(32) null
);


insert into tst_unpivot values ( 'Bob', 'Bob1A', 'Bob1B', 'Bob2A', 'Bob2B');
insert into tst_unpivot values ( 'Jon', 'Jon1A', 'Jon1B', 'Jon2A', 'Jon2B');
commit;

select a.name, a.descriptor, a, b
from 
  tst_unpivot unpivot (
  a for descriptor in
  (
    descriptor1a as 'Descriptor1', descriptor2a as 'Descriptor2'
  )
  ) a
  full outer join
  tst_unpivot unpivot (
  b for descriptor in
  (
    descriptor1b as 'Descriptor1', descriptor2b as 'Descriptor2'
  )
  ) b
on
  a.name = b.name
  and a.descriptor = b.descriptor;

Upvotes: 1

Girish
Girish

Reputation: 11

create table tst_unpivot
(
  name varchar(32) null,
  descriptor1a varchar(32) null,
  descriptor1b  varchar(32) null,
  descriptor2a  varchar(32) null,
  descriptor2b  varchar(32) null
)
go

insert into tst_unpivot values ( 'Bob', 'Bob1A', 'Bob1B', 'Bob2A', 'Bob2B')
insert into tst_unpivot values ( 'Jon', 'Jon1A', 'Jon1B', 'Jon2A', 'Jon2B')
go

select a.name, a.descriptor, a as 'A', b as 'B'
from 
  (select name,descriptor1a as Descriptor1,descriptor2a as Descriptor2 from  tst_unpivot) p1 
  unpivot 
  (
      a for descriptor in
      (
        Descriptor1, Descriptor2
      )
  ) a
  full outer join
  (select name,descriptor1b as Descriptor1,descriptor2b as Descriptor2 from  tst_unpivot) p2 
  unpivot 
  (
      b for descriptor in
      (
        Descriptor1, Descriptor2
      )
  ) b
on
  a.name = b.name
  and a.descriptor = b.descriptor

Upvotes: 0

Taryn
Taryn

Reputation: 247810

This process to convert columns into rows is known as an UNPIVOT. Since you are using SQL Server 2008, you can use CROSS APPLY with a VALUES constructor to get the result:

select type, A, B
from mytable
cross apply
(
  values
    ('Descriptor1', Descriptor1A, Descriptor1B),
    ('Descriptor2', Descriptor2A, Descriptor2B)
) c (type, A, B)
where name = 'Bob';

See SQL Fiddle with Demo

Upvotes: 1

valex
valex

Reputation: 24144

It's not clear what you want but here is a query to get your output results:

select 'DESCRIPTOR1' as Descr,
        DESCRIPTOR1A as A,
        DESCRIPTOR1B as B
from mytable
where Name='Bob'
UNION ALL
select 'DESCRIPTOR2' as Descr ,
        DESCRIPTOR2A as A,
        DESCRIPTOR2B as B 
from mytable
where Name='Bob'

SQL Fiddle demo

Or if you need it for all names:

select  Name,
        'DESCRIPTOR1' as Descr,
        DESCRIPTOR1A as A,
        DESCRIPTOR1B as B
from mytable

UNION ALL
select Name,
       'DESCRIPTOR2' as Descr ,
        DESCRIPTOR2A as A,
        DESCRIPTOR2B as B 
from mytable
ORDER BY 1,2

SQLFidle demo

Upvotes: 2

Related Questions