T-SQL动态透视表
T-SQL动态透视表
提问于 2012-08-31 15:24:52
好的，我有一张像这样的桌子
ItemID | ColumnName | Value
1 | name | Peter
1 | phone | 12345678
1 | email | peter@host.com
2 | name | John
2 | phone | 87654321
2 | email | john@host.com
3 | name | Sarah
3 | phone | 55667788
3 | email | sarah@host.com现在我需要把它变成这样：
ItemID | name | phone | email
1 | Peter | 12345678 | peter@host.com
2 | John | 87654321 | john@host.com
3 | Sarah | 55667788 | sarah@host.com我一直在研究动态轴心的例子，但我似乎无法将它们放入我的场景中。
有人能帮上忙吗？
回答 5
Stack Overflow用户
回答已采纳
发布于 2012-08-31 15:39:50
看一下下面的例子
CREATE TABLE #Table (
ID INT,
ColumnName VARCHAR(250),
Value VARCHAR(250)
)
INSERT INTO #Table SELECT 1,'name','Peter'
INSERT INTO #Table SELECT 1,'phone','12345678'
INSERT INTO #Table SELECT 1,'email','peter@host.com'
INSERT INTO #Table SELECT 2,'name','John'
INSERT INTO #Table SELECT 2,'phone','87654321'
INSERT INTO #Table SELECT 2,'email','john@host.com'
INSERT INTO #Table SELECT 3,'name','Sarah'
INSERT INTO #Table SELECT 3,'phone','55667788'
INSERT INTO #Table SELECT 3,'email','sarah@host.com'
---I assumed your tablename as TESTTABLE---
DECLARE @cols NVARCHAR(2000)
DECLARE @query NVARCHAR(4000)
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + t.ColumnName
FROM #Table AS t
--ORDER BY '],[' + t.ID
FOR XML PATH('')
), 1, 2, '') + ']'
SELECT @cols
SET @query = N'SELECT ID,'+ @cols +' FROM
(SELECT t1.ID,t1.ColumnName , t1.Value FROM #Table AS t1) p
PIVOT (MAX([Value]) FOR ColumnName IN ( '+ @cols +' ))
AS pvt;'
EXECUTE(@query)
DROP TABLE #TableStack Overflow用户
发布于 2012-08-31 15:55:24
试试这个：
SQL Server 2005+
;with
cte_name as(select * from <table> where ColumnName='name'),
cte_phone as(select * from <table> where ColumnName='phone'),
cte_email as(select * from <table> where ColumnName='email')
select n.ItemID,n.Value [Name],p.Value [Phone],e.Value [Email]
from cte_name n
join cte_phone p
on n.ItemID=p.ItemID
join cte_email e
on n.ItemID=e.ItemIDStack Overflow用户
发布于 2012-08-31 15:42:15
你不需要动态透视，因为它将是一个不同的表。只需像这样做：
name phone email
---------------------------------
Peter
123456
peter@host.com检查此SQL fiddle
SELECT DISTINCT u.ItemID, n.Value as 'name', p.Value as 'phone', e.Value as 'email'
FROM UserData u
INNER JOIN(
SELECT ItemID, Value
FROM UserData WHERE ColumnName = 'name') n ON n.ItemID = u.ItemID
INNER JOIN(
SELECT ItemID, Value
FROM UserData WHERE ColumnName = 'phone') p ON p.ItemID = u.ItemID
INNER JOIN(
SELECT ItemID, Value
FROM UserData WHERE ColumnName = 'email') e ON e.ItemID = u.ItemID页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/12210692
复制相关文章
相似问题