Mar 24

PIVOT Examples – SQL #Root #By Sayed Ahmed

http://stackoverflow.com/questions/24470/sql-server-pivot-examples

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

---


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

--

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

 

  From: http://sitestree.com/?p=2040
Categories:Root, By Sayed Ahmed
Tags:
Post Data:2015-06-14 21:36:12

Shop Online: https://www.ShopForSoul.com/
(Big Data, Cloud, Security, Machine Learning): Courses: http://Training.SitesTree.com
In Bengali: http://Bangla.SaLearningSchool.com
http://SitesTree.com
8112223 Canada Inc./JustEtc: http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning)
Shop Online: https://www.ShopForSoul.com/
Medium: https://medium.com/@SayedAhmedCanada