Sql query to convert column to rows

Question

This is my table

create table table1(action1 varchar(10),view1 varchar(10))

insert into table1 values('A1','VIEW'),('A1','EDIT'),('A2','VIEW'),('A3','VIEW'),('A3','EDIT')

I need output like this !

action1 VIEW    EDIT
A1  VIEW    EDIT
A2  VIEW    NULL
A3  VIEW    EDIT

I tried using pivot but I get error Msg 102, Level 15, State 1, Line 8 Incorrect syntax near '('.

Answer 1

   select * from table1
    pivot
    (
    max(view1)
    for view1 in([VIEW],[EDIT])
    )as piv;

See Demo

Answer 2

use a PIVOT function for get a column from row in sql query:

select action1,[VIEW] as 'VIEW' ,[EDIT] as 'EDIT' from table1
    pivot
    (
    max(view1)
    for view1 in([VIEW],[EDIT])
    )as piv;

Answer 3

By using MAX() function you can get the result

SELECT action1,
   MAX( CASE view1 WHEN 'View' THEN view1 ELSE '' END ) ViewCol, 
   MAX( CASE view1 WHEN 'Edit' THEN view1 ELSE null END ) EditCol 
   FROM table1 
   GROUP BY action1