SQL动态行转列

1.先以静态的方式实现行转列

select pro_id as pro_id , 

max(case buildup_type when '拉牌(下)' then buildup_chiname   end) [拉牌(下)] , 

max(case buildup_type when '拉牌(上) ' then buildup_chiname   end) [拉牌(上) ] , 

max(case buildup_type when '連接環' then buildup_chiname   end) [連接環] , 

max(case buildup_type when '頭制' then buildup_chiname   end) [頭制] , 

max(case buildup_type when '拉頭套件(下) ' then buildup_chiname   end) [拉頭套件(下) ] ,

  max(case buildup_type when '尾制' then buildup_chiname   end) [尾制] , 

max(case buildup_type when '開尾筒' then buildup_chiname   end) [開尾筒] , 

max(case buildup_type when '拉頭套件(上)' then buildup_chiname   end) [拉頭套件(上)] , 

max(case buildup_type when '碼莊 ' then buildup_chiname   end) [碼莊 ] , 

max(buildup_chiname)   from product_buildup group by pro_id

2.动态

declare @sql1 varchar(8000), @sql2 varchar(8000)

set @sql1 = 'select pro_id as pro_id,max(pro_chiname) as ' +'中文名 '

set @sql2 = ' '

select @sql1 = @sql1 + ',   max(case buildup_type when ''' + buildup_type + ''' then buildup_chiname   end) [' + buildup_type + ']' from (select distinct buildup_type from product_buildup) as a

select @sql2 =   ',   max(case buildup_type when ''' + buildup_type + ''' then buildup_chiname   end) [' + buildup_type + ']' from (select distinct buildup_type from product_buildup) as a

set @sql1 = @sql1 + '   from product_buildup   group by pro_id'

set @sql2 = @sql2 + '   from product_buildup group by pro_id'

exec(@sql1)

--print @sql1

print @sql2

3.列转行

select   id, painting_no_new, code, num_cur, fl_date as workdate, fl_work as workname

FROM         painting_work where fl_work<>''

union all

select   id, painting_no_new, code, num_cur,   ps_date as workdate, ps_work as workname

FROM         painting_work where ps_work<>''

order by workname