T-SQL动态枢轴

发布时间：2020-12-12 16:12:43 所属栏目：MsSql教程来源：网络整理

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!