T-SQL动态数据透视

Question

T-SQL动态数据透视

好的我有一张看起来像这样的桌子

ItemID | ColumnName | Value

1      | name       | Peter

1      | phone      | 12345678

1      | email      | [e-mail]

2      | name       | John

2      | phone      | 87654321

2      | email      | [e-mail]

3      | name       | Sarah

3      | phone      | 55667788

3      | email      | [e-mail]

现在我需要把它变成这个：

ItemID | name  | phone    | email

1      | Peter | 12345678 | [e-mail]

2      | John  | 87654321 | [e-mail]

3      | Sarah | 55667788 | [e-mail]

我一直在研究动态数据透视示例，但似乎我无法将它们融入我的场景中。

有人可以帮忙吗？

Answer 1

试试这个：SQL Server 2005+&nbsp;;with&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cte_name&nbsp;&nbsp;as(select&nbsp;*&nbsp;from&nbsp;<table>&nbsp;where&nbsp;ColumnName='name'), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cte_phone&nbsp;as(select&nbsp;*&nbsp;from&nbsp;<table>&nbsp;where&nbsp;ColumnName='phone'), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cte_email&nbsp;as(select&nbsp;*&nbsp;from&nbsp;<table>&nbsp;where&nbsp;ColumnName='email') &nbsp;&nbsp;select&nbsp;n.ItemID,n.Value&nbsp;[Name],p.Value&nbsp;[Phone],e.Value&nbsp;[Email]&nbsp; &nbsp;&nbsp;from&nbsp;&nbsp;cte_name&nbsp;n&nbsp;&nbsp;join&nbsp;&nbsp;cte_phone&nbsp;p&nbsp;&nbsp;on&nbsp;&nbsp;&nbsp;&nbsp;n.ItemID=p.ItemID&nbsp;&nbsp;join&nbsp;&nbsp;cte_email&nbsp;e&nbsp;&nbsp;on&nbsp;&nbsp;&nbsp;&nbsp;n.ItemID=e.ItemIDSQL小提琴演示

Answer 2

你不需要动态透视，因为它将是一个不同的表。只需做这样的事情：name&nbsp; &nbsp; phone&nbsp; &nbsp;email---------------------------------Peter&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; 123456&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[e-mail]检查这个SQL小提琴SELECT DISTINCT u.ItemID, n.Value as 'name', p.Value as 'phone', e.Value as 'email'FROM UserData uINNER JOIN(SELECT ItemID, Value&nbsp;FROM UserData WHERE ColumnName = 'name') n ON n.ItemID = u.ItemIDINNER JOIN(SELECT ItemID, Value&nbsp;FROM UserData WHERE ColumnName = 'phone') p ON p.ItemID = u.ItemIDINNER JOIN(SELECT ItemID, Value&nbsp;FROM UserData WHERE ColumnName = 'email') e ON e.ItemID = u.ItemID