pivot with dynamic column names in sql sever

Hi ,
               In previous article we see how to pivot data with static column name. now we will
discuss about how to do privot data with dynamic column name. for your better understanding
create below table and fill it with some data.

Create table ::

create table #color (Color_id int,Color_name varchar(50),color_prize int)

Insert Data::

insert into #color (Color_id,Color_name,color_prize) values (1,'black',1)
insert into #color (Color_id,Color_name,color_prize) values (1,'red',2)
insert into #color (Color_id,Color_name,color_prize) values (1,'wt',3)
insert into #color (Color_id,Color_name,color_prize) values (1,'re',3)
insert into #color (Color_id,Color_name,color_prize) values (1,'Grey',4)

insert into #color (Color_id,Color_name,color_prize) values (2,'black',6)
insert into #color (Color_id,Color_name,color_prize) values (2,'red',7)
insert into #color (Color_id,Color_name,color_prize) values (2,'wt',8)
insert into #color (Color_id,Color_name,color_prize) values (2,'re',9)
insert into #color (Color_id,Color_name,color_prize) values (2,'Grey',10)

get All column as comma separated string::

declare @Dynamic_column varchar(max)
select @Dynamic_column = COALESCE(@Dynamic_column+',' , '') +Color_name from (select distinct Color_name from #color)t
print @Dynamic_column

write a dynamic Query for pivot with dynamic columns::

declare @query varchar(max)
set @query ='select * from
(
select Color_id,Color_name,color_prize from #color
)te
pivot
(
min(color_prize) for Color_name in('+@Dynamic_column +')
) as d'
exec(@query)