sql - T-SQL PIVOT 在一列上附加其他列

Question

给定此表(始终有 2 个重复的组合):

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

我想查询它以获得这个:

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

我尝试过将 PIVOT 与动态查询结合使用，但无法得到令人满意的结果。

有人可以建议一下吗？

编辑:虽然 Ullas 解决方案对于组合对非常有效，但我想知道是否可以通过组合 N-uplet 获得相同的结果(例如 (0, 0, 0), (1, 1, 1), (2 , 2, 2) 应该产生 3 行)？我认为动态查询仍然是可行的方法，也许这次使用 PIVOT。

Answer 1

使用动态sql。
我刚刚创建了一个。不知道效率如何。

查询

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);

示例表格

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

结果集

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