求多行转成多列的实例的sql语句,该如何解决

求多行转成多列的实例的sql语句
table t 
  id price 
  1 2
  1 1
  2 0.1
  2 0.3
  2 0.4
  
  id price1 price2 price3
  1 2 1 NUll
  2 0.1 0.3 0.4
 
  想要用动态语句实现，因为ID号不止这两个，同时同一个ID号可能有更多的记录，谢谢各位赐教！！

[解决办法]

SQL code

select *,row_number() over (partition by id order by getdate()) as rnt into tp from tbdeclare @sql varchar(max)set @sql = 'select id'select @sql = @sql + ',max(case when rnt='+ltrim(rnt)+' then price else null end) [price'+ltrim(rnt)+']'from(select distinct rnt from tp)select @sql = @sql + ' from tp group by id'exec(@sql)drop table tp
[解决办法]
SQL code
 IF OBJECT_id(N'FENG') IS NOT NULL    DROP TABLE FENGGOCREATE TABLE FENG(id int,price VARCHAR(10))INSERT FENGSELECT 1,2 UNION ALLSELECT 1,1 UNION ALLSELECT 2,0.1 UNION ALLSELECT 2,0.3 UNION ALLSELECT 2,0.4GOIF OBJECT_ID('tempdb..#LSB') IS NOT NULLDROP TABLE #LSBGOSELECTROW = ROW_NUMBER() OVER (PARTITION BY id ORDER BY (SELECT 0)),*INTO #LSBFROM FENGDECLARE @XK VARCHAR(50);WITH AAAS (SELECT DISTINCT ROWFROM #LSB)SELECT @XK = ISNULL(@XK+',','')+QUOTENAME(ROW) FROM AA EXEC ('SELECT id,'+@XK+' FROM #LSB  PIVOT(MAX(price) FOR ROW IN ('+@XK+')) PIV ORDER BY 1')/*id    1    2    31    2.0    1.0    NULL2    0.1    0.3    0.4*/
[解决办法]
如果非常多的话 把@XK 定义大一些 8000内
[解决办法]
探讨

非常感谢楼上，但是我在SQl 2005下出现了错误，

消息 156，级别 15，状态 1，第 5 行
关键字 'select' 附近有语法错误。