unpivot multiple metric columns into 2 different columns and introduce a label to track column name for both columns

Question

source

id  value_a1    value_a2    value_a3    value_a4    value_b1    value_b2    value_b3    value_b4

1     1            1          1           1           1            1           1    
2     1            1          1           1           1            1           1    
3     1            1          1           1           1            1           1        

target

id  value1  value1label value2  value2label
1    1      value_a1      1      value_b1
1    1      value_a2      1      value_b2
1    1      value_a3      1      value_b3
1    1      value_a4      1      value_b4
2    1      value_a1      1      value_b1
2    1      value_a2      1      value_b2
2    1      value_a3      1      value_b3
2    1      value_a4      1      value_b4
3    1      value_a1      1      value_b1
3    1      value_a2      1      value_b2
3    1      value_a3      1      value_b3
3    1      value_a4      1      value_b4

I wrote down a query:

SELECT ID value1,
       value1label,
       value2,
       value2label,
FROM
  (SELECT id,
          valuea1,
          valuea2,
          valuea3,
          valuea4,
          valueb1,
          valueb2,
          valueb3,
          valueb4
   FROM TABLE) P UNPIVOT (value1
                          FOR value1label IN (valuea1,valuea2,valuea3,valuea4))AS UNPVT UNPIVOT (value2
                                                                                                 FOR value2label IN (valueb1,valueb2,valueb3,valueb4))AS UNPVT1

but the issue is value1label and value2label are not in sync as in a1 to b1 and using order on any one disrupt the ordering. if i order on value1label it goes from a1 then b1 and then b2,b3 and so on.

using union with unpivot value for each label results in null values as well which is not desired as it increase the number of rows

anyone can help me with this

p.s treat a1,a2,a3,a4 as alias for column heading for value_a1,value_a2,value_a3,value_a4 same is the case for b1,b2,b3,b4 for simplicity i chose those values.

Answer 1

You did not specify what version of SQL Server you are using but you can unpivot the pairs of data value_a1/value_b1 using CROSS APPLY instead of UNPIVOT.

If you are using SQL Server 2008+, then you can use the VALUES clause with CROSS APPLY, but you can also use UNION ALL:

select id, value1, value1label, value2, value2label
from yourtable
cross apply
(
  select value_a1, 'value_a1', value_b1, 'value_b1' union all
  select value_a2, 'value_a2', value_b2, 'value_b2' union all
  select value_a3, 'value_a3', value_b3, 'value_b3' union all
  select value_a4, 'value_a4', value_b4, 'value_b4'
) c (value1, value1label, value2, value2label);

See SQL Fiddle with Demo. This gets your data into the same rows as you need:

| ID | VALUE1 | VALUE1LABEL | VALUE2 | VALUE2LABEL |
|----|--------|-------------|--------|-------------|
|  1 |      1 |    value_a1 |      1 |    value_b1 |
|  1 |      1 |    value_a2 |      1 |    value_b2 |
|  1 |      1 |    value_a3 |      1 |    value_b3 |
|  1 |      1 |    value_a4 |      1 |    value_b4 |