T-SQL动态枢轴 | 动态枢轴

本文介绍了T-SQL动态枢轴的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

好的，我有一个像这样的表

Ok I have a table that looks like this

ItemID | ColumnName | Value
1      | name       | Peter
1      | phone      | 12345678
1      | email      | [email protected]
2      | name       | John
2      | phone      | 87654321
2      | email      | [email protected]
3      | name       | Sarah
3      | phone      | 55667788
3      | email      | [email protected]

现在我需要把它变成这样:

Now I need to turn that into this:

ItemID | name  | phone    | email
1      | Peter | 12345678 | [email protected]
2      | John  | 87654321 | [email protected]
3      | Sarah | 55667788 | [email protected]

我一直在研究动态枢轴示例，但是我似乎无法使其适合我的情况.

I have been looking at dynamic pivot examples, but it seems Im not able to fit them into my scenario.

任何人都可以帮忙吗?

推荐答案

看看下面的例子

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','[email protected]'
INSERT INTO #Table SELECT 2,'name','John'
INSERT INTO #Table SELECT 2,'phone','87654321'
INSERT INTO #Table SELECT 2,'email','[email protected]'
INSERT INTO #Table SELECT 3,'name','Sarah'
INSERT INTO #Table SELECT 3,'phone','55667788'
INSERT INTO #Table SELECT 3,'email','[email protected]'

---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

这篇关于T-SQL动态枢轴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！