将两个表连接后，将一个表的行转置为列名

Question

我正在尝试连接两个表格

ColumnNames
ID  |Name        |Type    |Status |
-----------------------------------------------------
1   |Fullname    |varchar |Active
2   |Email       |varchar |Active  
3   |Position    |varchar |Active  
4   |Category    |varchar |Active

ColumnValues
ID  |ColumnNameID |value                |Status |
-----------------------------------------------------
1   |1            |Linda                |Active
2   |2            |[e-mail]      |Active  
3   |3            |Soft Eng.            |Active  
4   |4            |Cat 1                |Active
5   |1            |Remil                |Active
6   |2            |[e-mail]      |Active  
7   |3            |Senior Soft Eng.     |Active  
8   |4            |Cat 2                |Active   
9   |1            |Ash                  |Active
10  |2            |[e-mail]        |Active  
11  |3            |Soft Eng.            |Active  
12  |4            |Cat 1                |Active  

然后将数据行（从ColumnNames表中的Name开始）转置为列名。

现在，我被告知要使用枢轴表。我已经成功地使用枢轴表将单个表中的行转置，但是我对如何将联接表中的行转置感到困惑。

下面是我尝试过的代码，但它给出了一个错误，说找不到列：

    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(ColumnName) 
                    from tm.ColumnName
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ColumnNameID, ' + @cols + ' from 
             (
                select i.ColumnNameID, i.ColumnValue
                from tm.ColumnValue i
                inner join tm.ColumnName a
                  on i.ColumnNameID = a.ColumnNameID
            ) x
            pivot 
            (
                max(ColumnNameID)
                for ColumnValue in (' + @cols + ')
            ) p '

execute(@query)

预期结果应为：

Fullname  |Email                 |Position             |Category |
--------------------------------------------------------------------
Linda     |[e-mail]       |Soft Eng.            |Cat 1
Remil     |[e-mail]       |Senior Soft Eng.     |Cat 2
Ash       |[e-mail]         |Soft Eng.            |Cat 1

我在这种情况下正确地使用了数据透视表吗？

- CinnamonRii

Answer 1

考虑在每个列值类型（fullname、email、position、category）上使用ROW_NUMBER进行排名，以包括在INNER JOIN中，然后通过PIVOT运行。无需动态构建SQL。

SELECT [Fullname], [Email], [Position], [Category]
FROM (
    SELECT i.ColumnValue, a.[Name],
           ROW_NUMBER() OVER (PARTITION BY a.[Name] ORDER BY a.ColumnNameID) AS rn
    FROM ColumnValues i
    INNER JOIN ColumnNames a
      ON i.ColumnNameID = a.ColumnNameID
  ) tbl

PIVOT
   (MAX(ColumnValue) 
    FOR [Name] IN ([Fullname], [Email], [Position], [Category])
   ) pvt
ORDER BY rn

Rextester演示

对于动态查询，构建@cols字符串以放置在透视查询的特定位置。

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[Name]) 
            FROM ColumnNames c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
             ,1,1,'')

SET @query = 'SELECT ' + @cols + '
                FROM (
                    SELECT i.ColumnValue, a.[Name],
                           ROW_NUMBER() OVER (PARTITION BY a.[Name] ORDER BY a.ColumnNameID) AS rn
                    FROM ColumnValues i
                    INNER JOIN ColumnNames a
                      ON i.ColumnNameID = a.ColumnNameID
                  ) tbl

                PIVOT
                   (MAX(ColumnValue) 
                    FOR [Name] IN (' + @cols + ')
                   ) pvt
                ORDER BY rn'

EXECUTE(@query)

Rextester demo

的翻译是：

{{链接1：Rextester演示}}

。

- Parfait

---

非常感谢您的回答，不过ColumnNames表中的“Name”是不固定的，因此我需要动态处理。 - CinnamonRii

这解释了你尝试动态查询的原因。我来回答：我已经成功地使用了透视表来转置单个表中的行，但是我不知道如何转置连接表中的行。 - Parfait

非常抱歉没有提到我需要动态SQL的需求。但是更新后的答案输出了预期的结果。非常感谢！ - CinnamonRii