SQL行转列

動態行轉列:

DECLARE @sql VARCHAR(500)

SET @sql='select pack_id'

SELECT @sql=@sql+',max(case pmat_id when '''+pmat_id+''' then pmat_name else '''' end)['+pmat_id+']'

from(SELECT DISTINCT pmat_id FROM pack_instruction_data)a

SET @sql=@sql+' from pack_instruction_data group by pack_id'

exec(@sql)

靜態行轉列:

SELECT pack_id,

max(CASE pmat_id WHEN 'JED0200044' THEN pmat_name ELSE '' end)pmat_id1,

max(CASE pmat_id WHEN 'JEH0100010' THEN pmat_name ELSE '' end)pmat_id2,

max(CASE pmat_id WHEN 'JED0200045' THEN pmat_name ELSE '' end)pmat_id3

FROM pack_instruction_data

GROUP BY pack_id


---行轉列(动态复杂一点)
--第一個例子
       DECLARE @sql VARCHAR(max),@e_date datetime,@query_date varchar(10),@month_date varchar(20),@i int
       select @i=0
       SET @sql='select a.cust_id,c.cust_sname,a.year_row,(isnull(b.pn_count_avg,0))/12 as pn_count_avg,(isnull(b.avg_amount,0))/12 as pn_amount_avg,b.avg_amount_bl
                 ,max(case d.type when 1 then d.pn_amount else 0 end) as year_amount1,
                       max(case d.type when 2 then d.pn_amount else 0 end) as year_amount2,
                       max(case d.type when 3 then d.pn_amount else 0 end) as year_amount3 '
       while @i<12
       begin
           set @e_date=DATEADD(m,@i*(-1),@bdate)
              set @mon
th_date=CONVERT(varchar(4),year(@e_date),120)+'年'+CONVERT(varchar(2),month(@e_date),120)+'月'
              set @query_date=convert(varchar(10),@e_date,23)
              SELECT @sql=@sql+',max(case convert(varchar(10),a.year_month,23) when '''+@query_date+''' then a.pn_count else 0 end)[pn_count'+cast(@i as varchar)+'],
                                 max(case convert(varchar(10),a.year_month,23) when '''+@query_date+''' then a.pn_amount else 0 end)[pn_amount'+cast(@i as varchar)+'],
                                            max(case convert(varchar(10),a.year_month,23) when '''+@query_date+''' then a.amount_bl else 0 end)[amount_bl'+cast(@i as varchar)+'],
                                            '''+@month_date+'''[month_date'+cast(@i as varchar)+']'
              set @i=@i+1
       end
       SET @sql=@sql+'FROM #pn_final a left join #total_avg_bl b on a.year_row=b.year_row and a.cust_id=b.cust_id
                      left join cust c on a.cust_id=c.cust_id
                              left join #pn_year_amount as d on a.cust_id=d.cust_id and a.year_row=d.year_row
                            GROUP BY a.year_row,b.avg_amount_bl,a.cust_id,c.cust_sname,b.pn_count_avg,b.avg_amount order by a.cust_id,a.year_row'
 
       exec(@sql)


---行轉列(动态复杂一点)
--第二個例子
  create table #add_table(begin_year int,begin_month int,team varchar(10),tool_count int)
       insert into #add_table
       select year(a.actual_transfer_time) as begin_year,month(a.actual_transfer_time) as begin_month,a.team,COUNT(*) as tool_count
       from project_follow_up as a 
       where a.actual_transfer_time>='2019-01-01'
    group by year(a.actual_transfer_time) ,month(a.actual_transfer_time),a.team
 DECLARE @sql VARCHAR(max),@i int
       select @i=1
       SET @sql='select cast(begin_year as varchar)+''年'' as begin_year,sum(tool_count) as total_tool,sum(case team when ''A'' then tool_count else 0 end) as total_tool_a,sum(case team when ''B'' then tool_count else 0 end) as total_tool_b,sum(case team when ''C'' then tool_count else 0 end) as total_tool_c
                    ,sum(case team when ''D'' then tool_count else 0 end) as total_tool_d,sum(case team when ''E'' then tool_count else 0 end) as total_tool_e,sum(case team when ''F'' then tool_count else 0 end) as total_tool_f  '
       while @i<=12
       begin         
           SELECT @sql=@sql+',max(case cast(begin_month as varchar)+team when '''+cast(@i as varchar)+'''+''A'' then tool_count else 0 end)[tool_count_a'+cast(@i as varchar)+'],
                              max(case cast(begin_month as varchar)+team when '''+cast(@i as varchar)+'''+''B'' then tool_count else 0 end)[tool_count_b'+cast(@i as varchar)+'],
                              max(case cast(begin_month as varchar)+team when '''+cast(@i as varchar)+'''+''C'' then tool_count else 0 end)[tool_count_c'+cast(@i as varchar)+'],
                              max(case cast(begin_month as varchar)+team when '''+cast(@i as varchar)+'''+''D'' then tool_count else 0 end)[tool_count_d'+cast(@i as varchar)+'],
                                               max(case cast(begin_month as varchar)+team when '''+cast(@i as varchar)+'''+''E'' then tool_count else 0 end)[tool_count_e'+cast(@i as varchar)+'],
                                                  max(case cast(begin_month as varchar)+team when '''+cast(@i as varchar)+'''+''F'' then tool_count else 0 end)[tool_count_f'+cast(@i as varchar)+'],
                                                  sum(case cast(begin_month as varchar) when '''+cast(@i as varchar)+''' then tool_count else 0 end)[tool_count_month'+cast(@i as varchar)+']'
              set @i=@i+1
       end
       SET @sql=@sql+'FROM #add_table 
                      GROUP BY begin_year order by begin_year '
 
       exec(@sql)
drop table #add_table