假设有张OS统计表(OS_detail)如下:
dept | pc_nam | pc_type | pc_OS | pc_qty |
人事部 | PER01 | DELL GX260 | WinXP | 1 |
人事部 | PER02 | DELL GX260 | Win2K | 1 |
工程部 | ME01 | DELL GX260 | Win2K | 1 |
工程部 | ME02 | DELL GX260 | Win98 | 1 |
工程部 | ME03 | DELL GX520 | WinXP | 1 |
生产部 | PRD01 | 810msi | Win98 | 1 |
生产部 | PRD02 | DELL GX280 | WinXP | 1 |
行政部 | ADM01 | DELL GX270 | Win2K | 1 |
行政部 | ADM02 | Dell Dimension 4100 | Win2K | 1 |
采购部 | PUR01 | COMPAQ DESKPRO | Win98 | 1 |
采购部 | PUR02 | COMPAQ DESKPRO | Win98 | 1 |
会计部 | ACT01 | COMPAQ DESKPRO | Win98 | 1 |
会计部 | ACT02 | DELL GX270 | WinXP | 1 |
会计部 | ACT03 | COMPAQ DESKPRO | Win98 | 1 |
IT部 | EDP01 | DELL GX260 | Win2K | 1 |
想变成如下:
dept | Win2K | Win98 | WinXP |
采购部 | NULL | 2 | NULL |
IT部 | 1 | NULL | NULL |
工程部 | 1 | 1 | 1 |
会计部 | NULL | 2 | 1 |
人事部 | 1 | NULL | 1 |
生产部 | NULL | 1 | 1 |
行政部 | 2 | NULL | NULL |
declare
@sql
varchar
(
4000
)
set @sql = ' select dept '
select @sql = @sql + ' ,sum(case pc_OS when ''' + pc_OS + ''' then pc_qty end) [ ' + pc_OS + ' ] ' from ( select distinct pc_OS from OS_detail) as a
select @sql = @sql + ' from OS_detail group by dept '
exec ( @sql )
set @sql = ' select dept '
select @sql = @sql + ' ,sum(case pc_OS when ''' + pc_OS + ''' then pc_qty end) [ ' + pc_OS + ' ] ' from ( select distinct pc_OS from OS_detail) as a
select @sql = @sql + ' from OS_detail group by dept '
exec ( @sql )