T-SQL dynamic pivot
13,795
Solution 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','[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
Solution 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
Author by
Nicolai Heilbuth
Updated on June 07, 2022Comments
-
Nicolai Heilbuth about 2 years
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.
Can anyone help?
-
Nicolai Heilbuth almost 12 yearsThanks a lot, that was exactly what I was looking for!
-
Nicolai Heilbuth almost 12 yearsThe problem is that the ColumnNames are dynamic. Both their names and the number of columns will differ.
-
Nicolai Heilbuth almost 12 yearsThe problem is that the ColumnNames are dynamic. Both their names and the number of columns will differ.