SQL Server: Examples of PIVOTing String data

Question

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?

Take a look at this link : http://dotnetgalactics.wordpress.com/2009/10/23/using-sql-server-20052008-pivot-on-unknown-number-of-columns-dynamic-pivot/ Might be helpful ;) — Pato, Oct 26 '09 at 13:35
You can see this link if the number of distinct items is unknown, meaning no of columns after pivoting is dynamic. [SQL Server Pivot: Converting Rows to Columns with Dynamic Query](http://blog.programmingsolution.net/sql-server-2008/sql-server-pivot-converting-rows-to-columns-with-dynamic-query/) — maeenul, Oct 05 '11 at 01:08
Check out [Row To Column (PIVOT)](http://wiki.lessthandot.com/index.php/Row_To_Column_%28PIVOT%29) and [Column To Row (UNPIVOT)](http://wiki.lessthandot.com/index.php/Column_To_Row_%28UNPIVOT%29) — SQLMenace, Sep 02 '08 at 19:56

score 167 · Accepted Answer · answered Sep 02 '08 at 19:55

167

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

answered Sep 02 '08 at 19:55

John Hubert

2,204
2
17
18

+1 Dude ... you are seriously a genius. I wish I could have known this sooner, instead of having to learn how to PIVOT! – ashes999 Feb 28 '12 at 15:52
@Silmaril89 assume that 2nd column name in question is 'data' and 1st column is 'Action' – Iman Aug 30 '12 at 18:20
1

Is there any reason why I shouldn't use `...ELSE NULL END...` instead of `...ELSE '' END...`? – mo. Dec 10 '12 at 16:38
16

Which is faster, this or PIVOT? – Robert Jeppesen Dec 13 '12 at 22:55
Whoah, you just blew my mind. In the back of my head I had this idea of what I though SQL server "should just do darnit!", but thought it couldn't be done. Then, I saw this. – David Hay May 13 '13 at 21:12

score 56 · Answer 2 · edited Mar 08 '13 at 13:54

56

Table setup:

CREATE TABLE dbo.tbl (
    action VARCHAR(20) NOT NULL,
    view_edit VARCHAR(20) NOT NULL
);

INSERT INTO dbo.tbl (action, view_edit)
VALUES ('Action1', 'VIEW'),
       ('Action1', 'EDIT'),
       ('Action2', 'VIEW'),
       ('Action3', 'VIEW'),
       ('Action3', 'EDIT');

Your table: SELECT action, view_edit FROM dbo.tbl

Your table

Query without using PIVOT:

SELECT Action, 
[View] = (Select view_edit FROM tbl WHERE t.action = action and view_edit = 'VIEW'),
[Edit] = (Select view_edit FROM tbl WHERE t.action = action and view_edit = 'EDIT')
FROM tbl t
GROUP BY Action

Query using PIVOT:

SELECT [Action], [View], [Edit] FROM
(SELECT [Action], view_edit FROM tbl) AS t1 
PIVOT (MAX(view_edit) FOR view_edit IN ([View], [Edit]) ) AS t2

Both queries result:
enter image description here

edited Mar 08 '13 at 13:54

Bryan

17,112
7
57
80

answered Jun 01 '12 at 20:53

mxasim

2,153
21
15

Thanks for this rare, clear answer. it immedeately shows an example and the resulting datasets – real_yggdrasil Oct 07 '14 at 12:13
I know this is old, but this is clearest, best-illustrated pivot demonstration I've seen. – Stan Shaw Jan 02 '15 at 19:37

score 53 · Answer 3 · answered Sep 02 '08 at 20:52

If you specifically want to use the SQL Server PIVOT function, then this should work, assuming your two original columns are called act and cmd. (Not that pretty to look at though.)

SELECT act AS 'Action', [View] as 'View', [Edit] as 'Edit'
FROM (
    SELECT act, cmd FROM data
) AS src
PIVOT (
    MAX(cmd) FOR cmd IN ([View], [Edit])
) AS pvt

score 7 · Answer 4 · edited Mar 19 '12 at 17:56

7

From http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/:

SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT
( SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt) p
UNPIVOT
(QTY FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)
) AS Unpvt
GO

edited Mar 19 '12 at 17:56

casperOne

73,706
19
184
253

answered Aug 27 '11 at 09:12

saranya

71
1
1

1

extra bracket after the first Product) – whytheq Oct 30 '13 at 14:16

score 6 · Answer 5 · answered Aug 23 '08 at 19:44

Well, for your sample and any with a limited number of unique columns, this should do it.

select 
    distinct a,
    (select distinct t2.b  from t t2  where t1.a=t2.a and t2.b='VIEW'),
    (select distinct t2.b from t t2  where t1.a=t2.a and t2.b='EDIT')
from t t1

score 5 · Answer 6 · answered Dec 19 '13 at 17:57

5

With pivot_data as
(
select 
action, -- grouping column
view_edit -- spreading column
from tbl
)
select action, [view], [edit]
from   pivot_data
pivot  ( max(view_edit) for view_edit in ([view], [edit]) ) as p;

answered Dec 19 '13 at 17:57

Vishwanath Dalvi

35,388
41
123
155

score 0 · Answer 7 · answered Feb 28 '19 at 03:44

I had a situation where I was parsing strings and the first two positions of the string in question would be the field names of a healthcare claims coding standard. So I would strip out the strings and get values for F4, UR and UQ or whatnot. This was great on one record or a few records for one user. But when I wanted to see hundreds of records and the values for all usersz it needed to be a PIVOT. This was wonderful especially for exporting lots of records to excel. The specific reporting request I had received was "every time someone submitted a claim for Benadryl, what value did they submit in fields F4, UR, and UQ. I had an OUTER APPLY that created the ColTitle and the value fields below

PIVOT(
  min(value)
  FOR ColTitle in([F4], [UR], [UQ])
 )

SQL Server: Examples of PIVOTing String data

7 Answers7

Linked

Related