By Tim Cochran


2008-08-23 18:36:33 8 Comments

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?

6 comments

@mr_eclair 2013-12-19 17:57:43

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;

@mxasim 2012-06-01 20:53:00

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

@real_yggdrasil 2014-10-07 12:13:27

Thanks for this rare, clear answer. it immedeately shows an example and the resulting datasets

@Stan Shaw 2015-01-02 19:37:28

I know this is old, but this is clearest, best-illustrated pivot demonstration I've seen.

@saranya 2011-08-27 09:12:46

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

@whytheq 2013-10-30 14:16:22

extra bracket after the first Product)

@Miles D 2008-09-02 20:52:47

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

@John Hubert 2008-09-02 19:55:19

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

@adhanlon 2010-02-17 19:42:48

What does data represent?

@ashes999 2012-02-28 15:52:13

+1 Dude ... you are seriously a genius. I wish I could have known this sooner, instead of having to learn how to PIVOT!

@Iman Abidi 2012-08-30 18:20:09

@Silmaril89 assume that 2nd column name in question is 'data' and 1st column is 'Action'

@mo. 2012-12-10 16:38:20

Is there any reason why I shouldn't use ...ELSE NULL END... instead of ...ELSE '' END...?

@Robert Jeppesen 2012-12-13 22:55:03

Which is faster, this or PIVOT?

@David Hay 2013-05-13 21:12:26

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.

@vzczc 2008-08-23 19:44:24

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

Related Questions

Sponsored Content

44 Answered Questions

16 Answered Questions

[SOLVED] LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

32 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

38 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

24 Answered Questions

[SOLVED] How do I perform an IF...THEN in an SQL SELECT?

24 Answered Questions

22 Answered Questions

[SOLVED] Check if table exists in SQL Server

37 Answered Questions

7 Answered Questions

[SOLVED] SQL Server query - Selecting COUNT(*) with DISTINCT

Sponsored Content