数据如下：
代号 价格
a123 12.5
a123 15.3
b123 11.5
b123 15.2
b123 16.3
转化为如下格式
代号 价格1 价格2 价格3
a123 12.5 15.3
b123 11.5 15.2 16.3
------解决思路----------------------
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#tableA') is null
drop table #tableA
Go
Create table #tableA([代号] nvarchar(4),[价格] decimal(18,1))
Insert #tableA
select N'a123',12.5 union all
select N'a123',15.3 union all
select N'b123',11.5 union all
select N'b123',15.2 union all
select N'b123',16.3
Go
DECLARE @s NVARCHAR(MAX)
,@Rows INT
SELECT TOP 1
@Rows = COUNT(1)
FROM #tableA
GROUP BY [代号]
ORDER BY COUNT(1) DESC
SET @s = ''
WHILE @Rows > 0
SELECT @s = ',[价格' + RTRIM(@Rows)
+ ']=max(case when [Rows]=' + RTRIM(@Rows)
+ ' then [价格] end)' + @s
,@Rows = @Rows - 1
EXEC('select [代号]'+@s+' from (SELECT *,[Rows]=ROW_NUMBER()OVER(PARTITION BY [代号] ORDER BY [代号]) FROM #tableA ) as A group by [代号]')
/*
代号 价格1 价格2 价格3
a123 12.5 15.3 NULL
b123 11.5 15.2 16.3
*/
------解决思路----------------------
--2005以上版本
Create table #t(id nvarchar(4),price decimal(18,1))
Insert #t
select N'a123',12.5 union all
select N'a123',15.3 union all
select N'b123',11.5 union all
select N'b123',15.2 union all
select N'b123',16.3
select *,rn=row_number() over(partition by id order by Price) into #a from #t
declare @s varchar(max)
select @s=isnull(@s+',','')+'max(case when rn='''+convert(varchar,rn)+'''then price end) as [price '+convert(varchar,rn)+']'
from #a group by rn
exec('select id,'+@s+' from #a group by id')
------解决思路----------------------
好久没写语句了，练习一下！
if not object_id(N'Tempdb..#tableA') is null
drop table #tableA
Go
Create table #tableA([代号] nvarchar(4),[价格] decimal(18,1))
Insert #tableA
select N'a123',12.5 union all
select N'a123',15.3 union all
select N'b123',11.5 union all
select N'b123',15.2 union all
select N'b123',16.3
go
select t .代号 , max (case when t .ID =1 then 价格 end ) as 价格1
, max (case when t .ID =2 then 价格 end ) as 价格2
, max (case when t .ID =3 then 价格 end ) as 价格3
from
(
select * ,ROW_NUMBER () over (partition by 代号 order by getdate () ) as ID from #tableA
)t group by t.代号
--代号 价格1 价格2 价格3
--a123 12.5 15.3 NULL
--b123 11.5 15.2 16.3