O pivô dynamic T-SQL

Ok, eu tenho uma tabela que se parece com isso

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 

Agora eu preciso transformar isso em:

 ItemID | name | phone | email 1 | Peter | 12345678 | peter@host.com 2 | John | 87654321 | john@host.com 3 | Sarah | 55667788 | sarah@host.com 

Eu tenho visto exemplos dynamics de pivô, mas parece que não consigo encaixá-los no meu cenário.

Alguém pode ajudar?

Dê uma olhada no exemplo a seguir

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

tente isto:

SQL Server 2005+

  ;with cte_name as(select * from  where ColumnName='name'), cte_phone as(select * from 
where ColumnName='phone'), cte_email as(select * from
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

Demonstração do SQL Fiddle

Você não precisa de um pivô dynamic, porque será uma tabela diferente. Simplesmente faça algo assim:

 name phone email --------------------------------- Peter 123456 peter@host.com 

Verifique este violino SQL

 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 

Aqui está uma consulta que estou usando para minha lista de contatos 🙂

 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 

Você já tentou isso:

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