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

User · Answer

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

User · Answer

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

User · Answer

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     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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] SQL Server: Examples of PIVOTing String data

Examples related to sql-server

Examples related to tsql

Examples related to pivot