CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
xmamat
xmamat

Reputation: 181

T-SQL PIVOT on one column to append the others

Given this table (there's always 2 duplicates of Combinations):

Combination Variable    Value
-----------------------------
0           a           1
0           b           2
1           c           3
1           d           4
2           e           5
2           f           6
...

I want to query it to get this:

Variable 0  Value 0     Variable 1  Value 1     Variable 2  Value 2     ...
---------------------------------------------------------------------------
a           1           c           3           e           5
b           2           d           4           f           6

I've tried using PIVOT with dynamic query but cannot come up with a satisfying result.

Can somebody please advise?

EDIT: though Ullas solution works perfectly for combination pairs, I was wondering if it's possible to achieve the same result with combination N-uplets (e.g. (0, 0, 0), (1, 1, 1), (2, 2, 2) should result in 3 rows)? I reckon dynamic query is still the way to go, maybe with PIVOT this time.

Upvotes: 4

Views: 416

Answers (1)

Ullas
Ullas

Reputation: 11556

Use dynamic sql.
I just created one. Don't know how efficient it is.

Query

declare @query1 varchar(max);
declare @query2 varchar(max);

select @query1 = 'select ' + 
 STUFF
 (
   (
        select distinct 
        ',min(t.Variable' + cast(Combination as varchar(6)) + ') as Variable' + 
        cast(Combination as varchar(6))  +
        ',min(t.Value' + cast(Combination as varchar(6)) + ') as Value' + 
        cast(Combination as varchar(6)) 
          from tblComb 
          for xml path('')
      ),
     1,1,'');
select @query1 += ' from('
select @query1 += 'select '+
 stuff
 (
    (
        select distinct 
        ',max(case when Combination = ' + cast(Combination as varchar(6)) +' 
        then Variable end) as Variable' + cast(Combination as varchar(6)) + 
        ',max(case when Combination = ' + cast(Combination as varchar(6)) +' 
        then Value end) as Value' + cast(Combination as varchar(6))
        from tblComb
        for xml path('')
        ), 
        1, 1, '');

select @query1 += ' from tblComb group by Combination, Variable)t union all ';

select @query2 = 'select ' + 
 STUFF
  (
    (
        select distinct 
        ',max(t.Variable' + cast(Combination as varchar(6)) + ') as Variable' + 
         cast(Combination as varchar(6))  +
        ',max(t.Value' + cast(Combination as varchar(6)) + ') as Value' + 
         cast(Combination as varchar(6)) 
         from tblComb 
         for xml path('')
         ), 
         1, 1, '');
select @query2 += ' from('
select @query2 += 'select '+
 stuff
  (
    (
        select distinct 
        ',max(case when Combination = ' + cast(Combination as varchar(6)) +' 
        then Variable end) as Variable' + cast(Combination as varchar(6)) + 
        ',max(case when Combination = ' + cast(Combination as varchar(6)) +' 
        then Value end) as Value' + cast(Combination as varchar(6))
        from tblComb
        for xml path('')
        ), 
        1, 1, '');

select @query2 += ' from tblComb group by Combination, Variable)t;';
select @query1 += @query2;                                      
execute(@query1);

Sample table

+-------------+----------+-------+
| Combination | Variable | Value |
+-------------+----------+-------+
| 0           | a        | 1     |
| 0           | b        | 2     |
| 1           | c        | 3     |
| 1           | d        | 4     |
| 2           | e        | 5     |
| 2           | f        | 6     |
+-------------+----------+-------+

Result set

+-----------+--------+-----------+--------+-----------+--------+
| Variable0 | Value0 | Variable1 | Value1 | Variable2 | Value2 |
+-----------+--------+-----------+--------+-----------+--------+
| a         | 1      | c         | 3      | e         | 5      |
| b         | 2      | d         | 4      | f         | 6      |
+-----------+--------+-----------+--------+-----------+--------+

Upvotes: 6

Related Questions