我正在尝试连接两个表格
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开始)转置为列名。
现在,我被告知要使用枢轴表。我已经成功地使用枢轴表将单个表中的行转置,但是我对如何将联接表中的行转置感到困惑。
下面是我尝试过的代码,但它给出了一个错误,说找不到列:
@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
我在这种情况下正确地使用了数据透视表吗?