SQL Server: Examples of PIVOTing String data

sql-server tsql pivot
Tim Cochran picture Tim Cochran · Aug 23, 2008 · Viewed 192.6k times · Source

Trying to find some simple SQL Server PIVOT examples. Most of the examples that I have found involve counting or summing up numbers. I just want to pivot some string data. For example, I have a query returning the following.

Action1 VIEW  
Action1 EDIT  
Action2 VIEW  
Action3 VIEW  
Action3 EDIT

I would like to use PIVOT (if even possible) to make the results like so:

Action1 VIEW EDIT  
Action2 VIEW NULL  
Action3 VIEW EDIT

Is this even possible with the PIVOT functionality?

Answer

John Hubert picture John Hubert · Sep 2, 2008

Remember that the MAX aggregate function will work on text as well as numbers. This query will only require the table to be scanned once.

SELECT Action,
       MAX( CASE data WHEN 'View' THEN data ELSE '' END ) ViewCol, 
       MAX( CASE data WHEN 'Edit' THEN data ELSE '' END ) EditCol
 FROM t
 GROUP BY Action

Related questions

Simple way to transpose columns and rows in SQL?

How do I simply switch columns with rows in SQL? Is there any simple command to transpose? ie turn this result: Paul | John | Tim | Eric Red 1 5 1 3 Green 8 4 3 5 Blue 2 2 9 1 into this: Red | Green | Blue Paul 1 8 2 John 5 4 2 Tim 1 3 9 Eric 3 5 1 PIVOT seems too …

Read More
SQL Server dynamic PIVOT query?

I've been tasked with coming up with a means of translating the following data: date category amount 1/1/2012 ABC 1000.00 2/1/2012 DEF 500.00 2/1/2012 GHI 800.00 2/10/2012 DEF 700.00 3/1/2012 ABC 1100.00 into the following: date ABC DEF GHI 1/1/2012 1000.00 2/1/2012 500.00 2/1/2012 800.00 2/10/2012 700.00 3/1/2012 1100.00 The blank spots can be NULLs or blanks, either is fine, …

Read More
Understanding PIVOT function in T-SQL

I am very new to SQL. I have a table like this: ID | TeamID | UserID | ElementID | PhaseID | Effort ----------------------------------------------------- 1 | 1 | 1 | 3 | 5 | 6.74 2 | 1 | 1 | 3 | 6 | 8.25 3 | 1 | 1 | 4 | 1 | 2.23 4 | 1 | 1 | 4 | 5 | 6.8 5 | 1 | 1 | 4 | 6 | 1.5 And I was told to get data like this ElementID | PhaseID1 | PhaseID5 | PhaseID6 -------------------------------------------- 3 | NULL | 6.74 | 8.25 4 | 2.23 | 6.8 | 1.5 I understand I need to use …

Read More