请教一个行转列的问题
我的表(para_table)数据如下:
name no para
8704021C0 000125 mm
8704021C0 000220.2 mm
8704021C0 00039 mm
A755110A0 000118.2 mm
A755110A0 000216.3 mm
A755110A0 00033 mm
我想把数据转成如下的样子显示:
name para_001 para_002 para_003
8704021C0 25mm 20.2mm 9mm
A755110A0 18.2mm 16.3mm 3mm
谢谢!
[解决办法]
create table ta([name] varchar(10), [no] varchar(4), para varchar(10))
insert ta select '8704021C0 ', '0001 ', '25 mm '
insert ta select '8704021C0 ', '0002 ', '20.2 mm '
insert ta select '8704021C0 ', '0003 ', '9 mm '
insert ta select 'A755110A0 ', '0001 ', '18.2 mm '
insert ta select 'A755110A0 ', '0002 ', '16.3 mm '
insert ta select 'A755110A0 ', '0003 ', '3 mm '
declare @sql varchar(4000)
set @sql= ' '
select @sql=@sql+ ', '+quotename( 'para_ '+right([no],3))+ '=max(case right([no],3) when '+quotename(right([no],3), ' ' ' ')
+ ' then para else ' ' ' ' end) '
from ta group by right([no],3)
set @sql= 'select name '+@sql+ ' from ta group by name '
--print @sql
exec(@sql)
name para_001 para_002 para_003
---------- ---------- ---------- ----------
8704021C0 25 mm 20.2 mm 9 mm
A755110A0 18.2 mm 16.3 mm 3 mm
(2 行受影响)
[解决办法]
select [name],
max(case when right(no,3) = '001 ' then para else -1 end ) '[001] ',
max(case when right(no,3) = '002 ' then para else -1 end ) '[002] ',
max(case when right(no,3) = '003 ' then para else -1 end ) '[003] '
from t
group by [name]
