好的,我有一张看起来像这样的桌子
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
我一直在观察动态枢轴的例子,但似乎我不能适应我的场景.
谁能帮忙？
解决方法
看下面的例子
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,'John'
INSERT INTO #Table SELECT 2,'87654321'
INSERT INTO #Table SELECT 2,'john@host.com'
INSERT INTO #Table SELECT 3,'Sarah'
INSERT INTO #Table SELECT 3,'55667788'
INSERT INTO #Table SELECT 3,'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 #Table
