假如像这样的1个EXCEL文件.
xh mc hd gg sl
1 a 3 50x50 20
1 a 3 50X50 30
1 a 3 50X50 40怎样在一个表里实现成这样.
xh mc hd gg sl1 sl2 sl3
1 a 3 50x50 20 30 40
xh mc hd gg sl
1 a 3 50x50 20
1 a 3 50X50 30
1 a 3 50X50 40怎样在一个表里实现成这样.
xh mc hd gg sl1 sl2 sl3
1 a 3 50x50 20 30 40
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (xh int,mc varchar(11),hd int,gg varchar(11),sl int)
insert into #T
select 1,'a',3,'50x50',20 union all
select 1,'a',3,'50X50',30 union all
select 1,'a',3,'50X50',40;with T as
(
select id=row_number()over(partition by xh,mc,hd,gg order by xh,mc,hd,gg), * from #T
)
select
*
from
T as a
pivot
(max(sl) for id in ([1],[2],[3])) as b/*
xh mc hd gg 1 2 3
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 a 3 50x50 20 30 40
*/
declare @Sql nvarchar(4000)
set @sql =''
set @sql=@sql+'select xh,mc,hd,gg'
select @sql=@sql+','''+ convert(nvarchar,s1)+'''=sum(case when s1='''+cast( sl as nvarchar)+''' then s1 else 0 end ' from(select distinct s1 from 表)A
set @sql=@sql+' from 表 group by xh,mc,hd,gg order by xh'
EXEC(@sql)
create table #T (xh int,mc varchar(11),hd int,gg varchar(11),sl int)
insert into #T
select 1,'a',3,'50x50',20 union all
select 1,'a',3,'50X50',30 union all
select 1,'a',3,'50X50',40;
CREATE PROCEDURE Select_#T
AS
select distinct
xh,mc,hd,gg,
sl1=case sl when 20 then 20 else 20 end,
sl2=case sl when 30 then 30 else 30 end,
sl3=case sl when 40 then 40 else 40 end
from #T
GO
exec Select_#T