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

求多行转成多列的实例的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,[email protected]+' FROM #LSB  PIVOT(MAX(price) FOR ROW IN ([email protected]+')) PIV ORDER BY 1')/*id    1    2    31    2.0    1.0    NULL2    0.1    0.3    0.4*/
------解决方案--------------------
如果非常多的话 [email protected] 定义大一些 8000内
------解决方案--------------------
探讨
非常感谢楼上，但是我在SQl 2005下出现了错误，

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