CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
Cameron
Cameron

Reputation: 75

How to pivot or rotate columns in a table of strings in SQL

I was wondering how you would go about performing a pivot on a SQL table with two columns of strings like below:

    ID   Label                Text
   ----  ----------          ----------
   10    Lead Source          Internet
   10    Display              Washington
   10    Sale                 Brick
   10    Colour               Blue

Into This:

   id   Lead Source   Display     Sale    Colour
   --   -----------   -------     ----    ------
   10    Internet      Washington  Brick   Blue

Upvotes: 2

Views: 1108

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

If, your label has some limit of record, then you may use conditional aggregation:

select ID,
       max(case when label = 'Lead Source' then text end) [Lead Source],
       max(case when label = 'Display' then text end) [Display],
       max(case when label = 'Sale' then text end) [Sale],
       max(case when label = 'Colour' then text end) [Colour]
from table t 
group by ID;

Upvotes: 1

Sreenu131
Sreenu131

Reputation: 2516

Sample data

IF OBJECT_ID('tempdb..#t') iS NOT NULL
DROP TABLE #t
;With cte(ID, Label,[Text])
AS
(
SELECT 10,'Lead Source','Internet'      UNION ALL
SELECT 10,'Display'    ,'Washington'    UNION ALL
SELECT 10,'Sale'       ,'Brick'         UNION ALL
SELECT 10,'Colour'     ,'Blue'


)
SELECT ID, Label,[Text]
INTO #t FROM cte

Using Dynamic Sql

DECLARE @DyColumn Nvarchar(max),
        @Sql Nvarchar(max)


SELECT @DyColumn=STUFF((SELECT  DISTINCT ', '+QUOTENAME(Label) FROM #t ORDER BY  1 desc FOR XML PATH ('')),1,1,'')

SET @Sql='
SELECT ID,'+@DyColumn+' FRom
(
SELECT * FROM #t
)AS SRC
 PIVOT
 (
  MAX([Text]) FOR Label IN('+@DyColumn+')
 )AS Pvt1   
'
PRINT @Sql
EXEC(@Sql)

Result

ID  Sale    LeadSource  Display     Colour
------------------------------------------
10  Brick   Internet    Washington  Blue

Upvotes: 0

DxTx
DxTx

Reputation: 3357

You can do that using pivot function.

SELECT * 
FROM   tablename 
       PIVOT ( Max([text]) 
             FOR [label] IN ([Lead Source], [Display], [Sale], [Colour]) ) piv;

Upvotes: 1

Related Questions