<p>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.</p>

<pre><code>Action1 VIEW  
Action1 EDIT  
Action2 VIEW  
Action3 VIEW  
Action3 EDIT  
</code></pre>

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

<pre><code>Action1 VIEW EDIT  
Action2 VIEW NULL  
Action3 VIEW EDIT  
</code></pre>

<p>Is this even possible with the PIVOT functionality?</p>

[{'answer_id': 24538, 'author': 'vzczc', 'author_id': 224, 'author_profile': 'https://Stackoverflow.com/users/224', 'pm_score': 3, 'selected': False, 'text': "<p>Well, for your sample and any with a limited number of unique columns, this should do it.</p>\n\n<pre><code>select \n    distinct a,\n    (select distinct t2.b  from t t2  where t1.a=t2.a and t2.b='VIEW'),\n    (select distinct t2.b from t t2  where t1.a=t2.a and t2.b='EDIT')\nfrom t t1\n</code></pre>\n"}, {'answer_id': 40434, 'author': 'John Hubert', 'author_id': 4286, 'author_profile': 'https://Stackoverflow.com/users/4286', 'pm_score': 8, 'selected': True, 'text': "<p>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.</p>\n\n<pre><code>SELECT Action,\n       MAX( CASE data WHEN 'View' THEN data ELSE '' END ) ViewCol, \n       MAX( CASE data WHEN 'Edit' THEN data ELSE '' END ) EditCol\n FROM t\n GROUP BY Action\n</code></pre>\n"}, {'answer_id': 40625, 'author': 'Miles D', 'author_id': 3898, 'author_profile': 'https://Stackoverflow.com/users/3898', 'pm_score': 6, 'selected': False, 'text': "<p>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.)</p>\n\n<pre><code>SELECT act AS 'Action', [View] as 'View', [Edit] as 'Edit'\nFROM (\n    SELECT act, cmd FROM data\n) AS src\nPIVOT (\n    MAX(cmd) FOR cmd IN ([View], [Edit])\n) AS pvt\n</code></pre>\n"}, {'answer_id': 7213585, 'author': 'saranya', 'author_id': 915293, 'author_profile': 'https://Stackoverflow.com/users/915293', 'pm_score': 3, 'selected': False, 'text': '<p>From <a href="http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/" rel="noreferrer">http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/</a>:</p>\n\n<pre><code>SELECT CUST, PRODUCT, QTY\nFROM Product) up\nPIVOT\n( SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt) p\nUNPIVOT\n(QTY FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)\n) AS Unpvt\nGO\n</code></pre>\n'}, {'answer_id': 10857177, 'author': 'mxasim', 'author_id': 581836, 'author_profile': 'https://Stackoverflow.com/users/581836', 'pm_score': 6, 'selected': False, 'text': '<p>Table setup:</p>\n\n<pre><code>CREATE TABLE dbo.tbl (\n    action VARCHAR(20) NOT NULL,\n    view_edit VARCHAR(20) NOT NULL\n);\n\nINSERT INTO dbo.tbl (action, view_edit)\nVALUES (\'Action1\', \'VIEW\'),\n       (\'Action1\', \'EDIT\'),\n       (\'Action2\', \'VIEW\'),\n       (\'Action3\', \'VIEW\'),\n       (\'Action3\', \'EDIT\');\n</code></pre>\n\n<p>Your table: \n<code>SELECT action, view_edit FROM dbo.tbl</code></p>\n\n<p><img src="https://i.stack.imgur.com/UrEJU.jpg" alt="Your table">\n<br/></p>\n\n<p><strong>Query without using PIVOT:</strong> <br></p>\n\n<pre><code>SELECT Action, \n[View] = (Select view_edit FROM tbl WHERE t.action = action and view_edit = \'VIEW\'),\n[Edit] = (Select view_edit FROM tbl WHERE t.action = action and view_edit = \'EDIT\')\nFROM tbl t\nGROUP BY Action\n</code></pre>\n\n<p><strong>Query using PIVOT:</strong> <br></p>\n\n<pre><code>SELECT [Action], [View], [Edit] FROM\n(SELECT [Action], view_edit FROM tbl) AS t1 \nPIVOT (MAX(view_edit) FOR view_edit IN ([View], [Edit]) ) AS t2\n</code></pre>\n\n<p><strong>Both queries result:</strong> <br/>\n<img src="https://i.stack.imgur.com/fakmf.jpg" alt="enter image description here"></p>\n'}, {'answer_id': 20688536, 'author': 'Vishwanath Dalvi', 'author_id': 435559, 'author_profile': 'https://Stackoverflow.com/users/435559', 'pm_score': 3, 'selected': False, 'text': '<pre><code>With pivot_data as\n(\nselect \naction, -- grouping column\nview_edit -- spreading column\nfrom tbl\n)\nselect action, [view], [edit]\nfrom   pivot_data\npivot  ( max(view_edit) for view_edit in ([view], [edit]) ) as p;\n</code></pre>\n'}, {'answer_id': 54918063, 'author': '1994Nerd', 'author_id': 11081348, 'author_profile': 'https://Stackoverflow.com/users/11081348', 'pm_score': 0, 'selected': False, 'text': '<p>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</p>\n\n<pre><code>PIVOT(\n  min(value)\n  FOR ColTitle in([F4], [UR], [UQ])\n )\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/24470', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2626/']