How to UNPIVOT multiple columns into more than two columns?

Question

I have data in a SybaseASE 15.X database and am trying to query for tidy data. The data is structured like this in the Sybase table:

| Name | Foo_A | Foo_B | Foo_C | Bar_A | Bar_B | Bar_C |
--------------------------------------------------------
| abcd |    16 |    32 |    14 |    52 |    41 |    17 |
| ...  |   ... |   ... |   ... |   ... |   ... |   ... |

I am looking to query the data in a way such that it looks like this:

| Name | Class | FooVal | BarVal |
----------------------------------
| abcd | A     |     16 |     52 |
| abcd | B     |     32 |     41 |
| abcd | C     |     14 |     17 |
| ...  | ...   |    ... |    ... |

Now, I am already aware of and am using a UNION ALL, but what would be a more succinct and straightforward way of doing what appears to be a simple UNPIVOT?

As much as I've read on this website, MSDN documentation, SAP documentation, and SQL references, UNPIVOT is only for two-column output.

If there is any more information that would be helpful, please let me know. Thank you!

Answer 1

Sybase (now SAP) ASE does not have a unpivot capability (as you likely already know), and there's no support for vector functions (that could provide a 1-to-many row split operation).

In addition to Yogesh's union all solution, you may want to look at the performance for a cross join (cartesian product) with a 3-row pseudo table (assuming you only have 3 classes), eg:

-- setup

create table mytable(Name varchar(10), Foo_A int, Foo_B int, Foo_C int, Bar_A int, Bar_B int, Bar_C int)
go

insert mytable values ('abcd',16,32,14,52,41,17)
go

-- main query

select  m.Name,
        p.Class,
        case when p.Class = 'A' then m.Foo_A
             when p.Class = 'B' then m.Foo_B
             when p.Class = 'C' then m.Foo_C
        end as FooVal,
        case when p.Class = 'A' then m.Bar_A
             when p.Class = 'B' then m.Bar_B
             when p.Class = 'C' then m.Bar_C
        end as BarVal

from    mytable m,
        (select 'A' as Class
         union all
         select 'B'
         union all
         select 'C') p

order by m.Name, p.Class
go

 Name       Class FooVal      BarVal
 ---------- ----- ----------- -----------
 abcd       A              16          52
 abcd       B              32          41
 abcd       C              14          17

To understand how this works, run the following to see the result set generated by the join, then apply the case logic to see how the final rows are generated:

select  p.Class, m.*
from    mytable m,
        (select 'A' as Class
         union all
         select 'B'
         union all
         select 'C') p
order by m.Name, p.Class
go