T-SQL dynamic pivot

问题:

Ok I have a table that looks like this

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]

Now I need to turn that into this:

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

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

Can anyone help?

回答1:

Have a look at the following example

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

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

回答2:

try this:

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

SQL Fiddle Demo

回答3:

You don\'t need dynamic pivot, becuase it will be a different table. Simply do something like this:

name    phone   email
---------------------------------
Peter            
        123456
                 [e-mail]

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

回答4:

Here\'s a query I\'m using for my contactlist :)

SELECT *
FROM
    (
    SELECT Contact_Id AS CT
          , [Age]
          , [Sex]
          , [State]
          , [Country]
          , [Keyword]
          , [Married]
          , [Kids]
          , [Car]
     FROM
         (SELECT c.PropertyName
               , c.ValueString
               , c.Contact_Id
          FROM
              ContactProfiles c) AS ctp
         PIVOT (max(ctp.ValueString) FOR PropertyName IN ([Age], [Sex], [State], [Country], [Keyword], [Married], [Kids], [Car])) AS PivotTable
         ) AS pvt

WHERE
    pvt.[Age] > 18
    AND (pvt.[State] = \'CA\' OR pvt.[State] = \'NY\')
    AND pvt.[Sex] = \'F\'
    --*AND pvt.[Keyword] LIKE \'%B;%\'
    AND pvt.[Married] = \'True\'
    AND pvt.[Kids] > 0

回答5:

Have you tried this:

SELECT ItemID, name, phone, email
FROM
(SELECT [ItemID] ,[ColumnName] ,[Value] FROM Item) Item
PIVOT (MAX(Value) FOR ColumnName IN (name, phone, email) ) as pvt