时间戳

首页 > 解决方案 > 将两列的值转换为列名

sql-server - 将两列的值转换为列名

问题描述

我有一张如下表 

RowNamesCol1   RowNamesCol2   Col1   Col2  
Col1Row1       Col2Row1       1      4  
Col1Row2       Col2Row2       2      5  
Col1Row3       Col2Row3       3      6

我想出了以下查询：

SELECT * FROM (
SELECT * FROM (
SELECT RowNamesCol1, RowNamesCol2, colnames, colvalues FROM PivUnPiv1
UNPIVOT
(colvalues FOR colnames IN (col1, col2)) AS unpivtable) as a
PIVOT
(MAX(Colvalues) FOR RowNamesCol2 IN ([Col2Row1], [Col2Row2], [Col2Row3])) AS P
) AS b
PIVOT
(MAX(Colnames) FOR RowNamesCol1 IN ([Col1Row1], [Col1Row2], [Col1Row3])) AS P2

结果如下：

Col2Row1 | Col2Row2   |Col2Row3   |Col1Row1   |Col1Row2   |Col1Row3
-------------------------------------------------------------------
NULL     | NULL       |3          |NULL       |NULL       |Col1
NULL     | NULL       |6          |NULL       |NULL       |Col2
NULL     | 2          |NULL       |NULL       |Col1       |NULL
NULL     | 5          |NULL       |NULL       |Col2       |NULL
1        | NULL       |NULL       |Col1       |NULL       |NULL
4        | NULL       |NULL       |Col2       |NULL       |NULL

但我正在寻找如下：

________________________________________
| RNC1 |  Col1Row1 | Col1Row2 | Col1Row3|  
|---------------------------------------|  
| RNC2 |  Col2Row1 | Col2Row2 | Col2Row3|  
|---------------------------------------|  
| Col1 |  1        | 2        | 3       |  
|---------------------------------------|  
| Col2 |  4        | 5        | 6       |  
|---------------------------------------|

您的帮助将不胜感激。

标签： sql-server

解决方案

通过旋转可以这样完成：

SELECT *
FROM  
(SELECT ROW_NUMBER() over (ORDER BY (SELECT NULL)) as rn,
        -- add row number w/o any strict sorting just to somehow 
        -- separate one row from another and to use in PIVOT part
        RowNamesCol1, -- all columns should be same datatype
        RowNamesCol2, -- I use varchar(50) as example 
        CAST(Col1 as varchar(50)) as Col1, 
        CAST(Col2 as varchar(50)) as Col2  
FROM Test) AS SourceTable  
UNPIVOT  
   ([value] FOR [key] IN (RowNamesCol1, RowNamesCol2, Col1, Col2)  
) AS unpvt
PIVOT  -- you need as much numbers as rows in your table
(MAX ([value]) FOR rn IN ([1], [2], [3])  
) AS pvt

输出：

|          key |        1 |        2 |        3 |
|--------------|----------|----------|----------|
|         Col1 |        1 |        2 |        3 |
|         Col2 |        4 |        5 |        6 |
| RowNamesCol1 | Col1Row1 | Col1Row2 | Col1Row3 |
| RowNamesCol2 | Col2Row1 | Col2Row2 | Col2Row3 |

推荐阅读