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