具有列名和第一行值的 SQL 映射

Question

假设我有一个表：

C1   C2   C3   C4
--   --   --   --
v11  v12  v13  v14
v21  v22  v23  v24

我希望输出是列名和第一行映射的，如下所示：

        NC1   NC2
        --    --  
        C1    v11 
        C2    v12 
        C3    v13
        C4    v14

对此的 SQL 查询是什么？

我需要动态地执行此操作 - 实际上不知道表中有多少列！

Answer 1

根据上下文，我会使用以下解决方案之一：

-- Source Table
IF EXISTS (SELECT * FROM sys.tables t WHERE t.schema_id = 1 AND t.name = N'SourceTable_83648326')
BEGIN
    DROP TABLE dbo.SourceTable_83648326
END
SELECT  *
INTO    dbo.SourceTable_83648326
FROM (VALUES 
    ('v11', 'v12', 'v13', 'v14'),
    ('v21', 'v22', 'v23', 'v24')
) SourceTable(C1, C2, C3, C4)
WHERE C1 = 'v11' -- It select required source row
-- Solution #1: dynamic query
DECLARE @Columns NVARCHAR(MAX) = N''
SELECT  @Columns = @Columns + N', ' + QUOTENAME(c.name)
FROM    sys.tables t JOIN sys.columns c ON t.object_id = c.object_id
WHERE   t.schema_id = 1 -- dbo
AND     t.name = N'SourceTable_83648326' -- Don't use ORDER BY or DISTINCT !
SET @Columns = STUFF(@Columns, 1, 2, '')
DECLARE @SqlStatement NVARCHAR(MAX) = N'
SELECT  up.*
FROM (
    SELECT  *
    FROM    dbo.SourceTable_83648326
    WHERE   C1 = @p_C1 -- It select required source row
) ups -- UnPivot source
UNPIVOT( NC2_Value FOR NC1_ColumnType IN (' + @Columns + ') ) up -- UnPivot'
EXEC sp_executesql @SqlStatement, N'@p_C1 VARCHAR(50)', @p_C1 = 'v11' -- Replace with propper data type and max length

.

-- Solution #2: static query
SELECT  
    b.XmlCol.value('local-name(.)', 'SYSNAME') AS NC1_ColumnType,
    b.XmlCol.value('.', 'VARCHAR(50)') AS NC2_Value -- Replace with propper data type and max length
FROM (
    VALUES((SELECT  *
    FROM    dbo.SourceTable_83648326
    WHERE   C1 = 'v11' -- It select required source row
    FOR XML RAW, TYPE))
) a(XmlCol)
CROSS APPLY a.XmlCol.nodes(N'row/@*') b(XmlCol)

Answer 2

> 做一个UNION ALL：

select 'C1' as nc1, c1 as nc2 from tablename where c1 = 'v11'
union all
select 'C2', c2 from tablename where c1 = 'v11'
union all
select 'C3', c3 from tablename where c1 = 'v11'
union all
select 'C4', c4 from tablename where c1 = 'v11'

也许您需要在末尾添加ORDER BY nc1。

公用表表达式版本：

witc cte as
(
    select * from tablename where c1 = 'v11'
)
select 'C1' as nc1, c1 as nc2 from cte
union all
select 'C2', c2 from cte
union all
select 'C3', c3 from cte
union all
select 'C4', c4 from cte

Answer 3

动态版本的更新：

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql = 
'SELECT
    x.NC1, x.NC2
FROM tbl t
CROSS APPLY ( VALUES ' +
(SELECT STUFF((
    SELECT ',(''' +COLUMN_NAME + ''', ' + QUOTENAME(COLUMN_NAME) + ')'
    FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Tbl'            
    FOR XML PATH('')
), 1, 1, '')) +
') x (NC1, NC2)
WHERE
    t.C1 = ''V11''';
PRINT (@sql);
EXEC (@sql);

---

另一种方法是使用 CROSS APPLY .这更有效，因为它只扫描一次表：

SELECT 
    x.NC1, x.NC2
FROM tbl t
CROSS APPLY ( VALUES
    ('C1', C1),
    ('C2', C2),
    ('C3', C3),
    ('C4', C4)
) x(NC1, NC2)
WHERE
    t.C1 = 'V11'

参考：另一种选择（更好？Method to UNPIVOT （SQL Spackle） - SQLServerCentral