sql server - How to convert row to column in SQL? -

i have stored procedure (join 2 tables , select condition @gid), want convert table result rows columns. use dynamic pivot query.

my stored procedure:

after try using pivot

i want result this:

group_mod_id   add  edit  delete  etc... ---------------------------------------     g02            1     1    0      .... 

can give me advice ?

thank you.

it's because you're using batch delimiter separate queries. means scope of @gid incorrect. remove semi colon after:

declare @pivot_cols nvarchar(max); 

you don't need use batch delimiters in case. logical flow of procedure means can omit them without problems.

edit:

here's edited code i've devised:

alter procedure get_column_value @gid char(3) begin      declare @pivotcols nvarchar(max)      select @pivotcols = stuff((select distinct ' , ' + quotename(b.function_mod_name)     function_group     join function_mod b     on a.function_mod_id = b.function_mod_id     a.group_mod_id = @gid     xml path (' '), type).value(' . ', 'nvarchar(max) '), 1, 1, ' ')      declare @pivotquery nvarchar(max)      set @pivotquery = '     ;with cte (     select a.group_mod_id, b.function_mod_name, cast(allow bit) allow     function_group     join function_mod b     on a.function_mod_id = b.function_mod_id)     select group_mod_id, '+@pivotcols+'     cte     pivot (max(allow) function_mod_name in ('+@pivotcols')) piv'     print @pivotquery     exec (@pivotquery)  end 

edit2:

you should execute stored procedure so:

exec get_column_value @gid='g02'