sql-server - T-SQL 查询得到以下结果

Question

我有一个在面试中遇到的问题。

你能提供解决方案吗？

标签： sql-servertsql

Answer 1

我会尝试通过UNPIVOTing数据并使用ROW_Number来获取 ColumnName 的整数，如下所示

DECLARE @t TABLE ( Column1 CHAR(1), Column2 CHAR(3))
INSERT INTO @t
(Column1,Column2)
VALUES
('A', 'AAA') ,('B', 'BBB') ,('C', 'CCC') ,('D', 'DDD') 

SELECT 
     ColData = T.Column1
    ,ColName = 'Column1.' + CAST(ROW_NUMBER()OVER(ORDER BY (SELECT null)) AS NVARCHAR(5))
FROM @t T
UNION
SELECT 
     ColData = T.Column2
    ,ColName = 'Column2.' + CAST(ROW_NUMBER()OVER(ORDER BY (SELECT null)) AS NVARCHAR(5))
FROM @t T

这给出了这个结果

ColData ColName
A       Column1.1
AAA     Column2.1
B       Column1.2
BBB     Column2.2
C       Column1.3
CCC     Column2.3
D       Column1.4
DDD     Column2.4

因此，然后将其包装在 PIVOT 查询中以获得所需的输出

SELECT
    [Column1.1],[Column2.1],[Column1.2],[Column2.2],[Column1.3],[Column2.3],[Column1.4],[Column2.4]
FROM(
        SELECT 
             ColData = T.Column1
            ,ColName = 'Column1.' + CAST(ROW_NUMBER()OVER(ORDER BY (SELECT null)) AS NVARCHAR(5))
        FROM @t T
        UNION
        SELECT 
             ColData = T.Column2
            ,ColName = 'Column2.' + CAST(ROW_NUMBER()OVER(ORDER BY (SELECT null)) AS NVARCHAR(5))
        FROM @t T
)AS Tab
PIVOT 
(  
    MAX(ColData)  
    FOR ColName IN ([Column1.1],[Column2.1],[Column1.2],[Column2.2],[Column1.3],[Column2.3],[Column1.4],[Column2.4])  
) AS PIV;

输出

Column1.1   Column2.1   Column1.2   Column2.2   Column1.3   Column2.3   Column1.4   Column2.4
A            AAA         B           BBB         C           CCC         D           DDD