查同一表多条记录

[malimalihong]

想从表1数据得到表2

请问大神们，如何写SQL

[卖水果的net]

某个商品有 100 个价格，要增加 100 列吗？

[Dear SQL(燊)]

if object_id('tempdb..#data') is not null drop table #data
if object_id('tempdb..#list') is not null drop table #list

select ID=1,[name]='A',price=3.5 into #list union all
select ID=1,[name]='A',price=2 union all
select ID=1,[name]='A',price=2 union all
select ID=1,[name]='A',price=2.8 union all
select ID=2,[name]='b',price=100 union all
select ID=2,[name]='b',price=101 

;
with list as(
	select id,[name],price
	from #list
	group by id,[name],price
)
select *
	,rid='price'+cast(ROW_NUMBER()over(partition by ID,[name] order by price ) as varchar(50))
into #data
from list

declare @sql nvarchar(max),@colname nvarchar(max)=''
;
with list as(
	select distinct rid
	from #data
)
select @colname=@colname+',['+rid+']'
from list

print @colname

set @sql='
select *
from #data pivot(max(price) for rid in('+RIGHT(@colname,len(@colname)-1)+'))pt
'
exec(@sql)

/*
id          name price1                                  price2                                  price3
----------- ---- --------------------------------------- --------------------------------------- ---------------------------------------
1           A    2.0                                     2.8                                     3.5
2           b    100.0                                   101.0                                   NULL

(2 行受影响)
*/