Using SQL unpivot on two groups

Question

I have a table as below:

Name    ValueA1    ValueA2    ValueA3    ValueB1    ValueB2    ValueB3    QtyA1    QtyA2    QtyA3    QtyB1    QtyB2    QtyB3
John    1          2          3          4          5          6          100      200      300      150      250      350
Dave    11         12         13         14         15         16         100      200      300      150      250      350

I am able to use unpivot to get the values:

select [Name]
     , Replace(u.[Period],'Value','') as [Period]
     , u.[Value]
from Table1
unpivot
(
  [Value]
  for [Period] in ([ValueA1], [ValueA2], [ValueA3], [ValueB1], [ValueB2], [ValueB3])
  ) u;

SQL Fiddle

However I'm trying to get both the Value and Qty columns on a single row, what I want to end up with is:

Name    Number  Value   Qty
John    A1      1       100
John    A2      2       200
John    A3      3       300
John    B1      4       150
John    B2      5       250
John    B3      6       350
Dave    A1      11      100
Dave    A2      12      200
Dave    A3      13      300
Dave    B1      14      150
Dave    B2      15      250
Dave    B3      16      350

What I have so far is (which doesn't work at all):

select [Name]
     , Replace(u.[Period],'Value','') as [Period]
     , u.[Value]
     , u2.[Value]
from Table1
unpivot
(
  [Value]
  for [Period] in ([ValueA1], [ValueA2], [ValueA3], [ValueB1], [ValueB2], [ValueB3])
  ) u

 unpivot

 (
   [Qty]
   for [Period] in ([QtyA1], [QtyA2], [QtyA3], [QtyB1], [QtyB2], [QtyB3])

 ) u2;

Is what I am trying to do even possible with unpivot?

Answer 1

You can use a simple apply for this by specifying the pairs in a values clause:

declare @table table (Name varchar(10), ValueA1 int, ValueA2 int, QtyA1 int, QtyA2 int);
insert into @table
select 'John', 1, 2, 100, 200 union all     
select 'Dave', 11, 12, 100, 200;


select  Name, Number, Value, Qty 
from    @table
cross 
apply   (   values 
            ('A1', ValueA1, QtyA1),
            ('A2', ValueA2, QtyA2)
        ) c (number, value, qty);

If you're using an older edition of MSSQL, you might need to use this instead of the values clause above:

cross 
apply (   select 'A1', ValueA1, QtyA1 union all
          select 'A2', ValueA2, QtyA2
      ) c (number, value, qty);

Returns:

Name    Number  Value   Qty
John    A1      1       100
John    A2      2       200
Dave    A1      11      100
Dave    A2      12      200