连接两个表，然后将一个表中的行转置为列名 [英] Join Two Tables Then Transpose Rows From One Table As Column Names
问题描述
我正在尝试连接两个表
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 |linda@email.com |Active
3 |3 |Soft Eng. |Active
4 |4 |Cat 1 |Active
5 |1 |Remil |Active
6 |2 |Remil@email.com |Active
7 |3 |Senior Soft Eng. |Active
8 |4 |Cat 2 |Active
9 |1 |Ash |Active
10 |2 |ash@email.com |Active
11 |3 |Soft Eng. |Active
12 |4 |Cat 1 |Active
然后将数据行(来自 ColumnNames 表上的 Name)转换为列名.
and then transposing rows of data (from Name on ColumnNames table) as column names.
现在，有人告诉我使用 Pivot.我已经成功地使用数据透视从单个表中转置行，但我对如何转置连接表中的行感到困惑.
Now, I was told to use Pivot. I've successfully used pivot for transposing rows from a single table, but I'm confused in how to transpose rows from joined tables.
下面是我尝试过的代码，但它给了我一个错误，说列不存在
Below is the code that I've tried, but it gives me an error saying columns doesn't exist
@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 |linda@email.com |Soft Eng. |Cat 1
Remil |Remil@email.com |Senior Soft Eng. |Cat 2
Ash |ash@email.com |Soft Eng. |Cat 1
我在这种情况下是否正确使用了枢轴?
Am I utilizing pivot correctly on this scenario?
推荐答案
考虑在每个列值类型(全名、电子邮件、职位、类别)中使用 ROW_NUMBER 进行排名要包含在 INNER JOIN 中，然后通过 PIVOT 运行.无需动态构建 SQL:
Consider a ranking with ROW_NUMBER across each column value type (fullname, email, position, category) to be included in INNER JOIN then run through PIVOT. No need of dynamic building of 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
对于动态查询，构建一个 @cols 字符串以放置在数据透视查询的特定位置.
For a dynamic query, build a @cols string to place in specific places of pivot query.
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)
这篇关于连接两个表，然后将一个表中的行转置为列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
