[经常用此练习即可] SQL2000行转列三种方式解答，侧重于第二种方式，第一种需用临时表，第三种方式适合固定方式写入都正确

注：数据库为sql2000版本

----------------------------------------------------------------------------------------
问题
表T1

收费类型  排序

管理费         0

工资费         1

劳务费         2

表T2

单据号         日期           收费类型      金额

sk001   2015-11-15       管理费        100

sk002   2015-10-7         劳务费          50

sk002   2015-10-7         工资费        300



行转列得到T3表，其中，  T1.收费类型=T2.收费类型，按排序字段排序，请问如何写SQL语句

T3表

单据号      日期                管理费        工资费        劳务费 

sk001    2015-11-15          100             0                  0
sk002    2015-10-7               0             300              50

 

 

--------------------------------------------------------------------------------------------------

解决

方法一：

 

Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)

Insert Class

select N'张三',N'语文',78 union all

select N'张三',N'数学',87 union all

select N'张三',N'英语',82 union all

select N'张三',N'物理',90 union all

select N'李四',N'语文',65 union all

select N'李四',N'数学',77 union all

select N'李四',N'英语',65 union all

select N'李四',N'物理',85 

Go

--2000方法:

动态:

 

declare @s nvarchar(4000)

set @s=''

Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'

from Class group by[Course]

exec('select [Student]'+@s+' from Class group by [Student]')

 

 

生成静态:

 

select 

    [Student],

    [数学]=max(case when [Course]='数学' then [Score] else 0 end),

    [物理]=max(case when [Course]='物理' then [Score] else 0 end),

    [英语]=max(case when [Course]='英语' then [Score] else 0 end),

    [语文]=max(case when [Course]='语文' then [Score] else 0 end) 

from 

    Class 

group by [Student]

 

 

方法二：

 --测试数据

    create table #Table(

    单据号 varchar(15),

    日期 varchar(15),

    收费类型 varchar(15),

    金额 int

    )

    insert into #Table

select 'sk001',   '2015-11-15' ,      '管理费' ,       100 union all

select 'sk002' ,  '2015-10-7',         '劳务费' ,         50 union all

select 'sk002',   '2015-10-7' ,        '工资费',        300

create table #Table1(

    收费类型 varchar(15),

    排序 int

    )

insert into #Table1

select'管理费',         0 union all

select'工资费' ,        1 union all

select'劳务费' ,        2

 

    --查询

     

select a.*,b.排序 into #tab from #Table a left join #Table1 b on a.收费类型=b.收费类型

declare @sql varchar(max)  

set @sql=''

  

select @sql=@sql + ','+rtrim(收费类型)+'=sum(case 收费类型 when '''+rtrim(收费类型)+''' then 金额 else 0 end)' from #tab group by 收费类型,排序 order by 排序

   

exec('select 单据号,日期'+@sql+'from  #tab group by 单据号,日期 order by 单据号'  )  

 

DROP TABLE #Table 

DROP TABLE #Table1   

     

    --结果

    /*

    单据号             日期              管理费         工资费         劳务费

--------------- --------------- ----------- ----------- -----------

sk001           2015-11-15      100         0           0

sk002           2015-10-7       0           300         50

 

(2 行受影响)

    */

 

方法三：

----------------------------------------------------------------

-- Author  :fredrickhu(小F，向高手学习)

-- Date    :2015-12-02 12:00:38

-- Version:

--      Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 

--    Feb 10 2012 19:39:15 

--    Copyright (c) Microsoft Corporation

--    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

--

----------------------------------------------------------------

--> 测试数据：[T1]

if object_id('[T1]') is not null drop table [T1]

go 

create table [T1]([收费类型] varchar(6),[排序] int)

insert [T1]

select '管理费',0 union all

select '工资费',1 union all

select '劳务费',2

--> 测试数据：[T2]

if object_id('[T2]') is not null drop table [T2]

go 

create table [T2]([单据号] varchar(5),[日期] datetime,[收费类型] varchar(6),[金额] int)

insert [T2]

select 'sk001','2015-11-15','管理费',100 union all

select 'sk002','2015-10-7','劳务费',50 union all

select 'sk002','2015-10-7','工资费',300

--------------开始查询--------------------------

SELECT 

t2.单据号,CONVERT(VARCHAR(10),t2.日期 ,120) AS 日期,

SUM(CASE WHEN t2.收费类型='管理费' THEN 金额 ELSE 0 END) AS '管理费',

SUM(CASE WHEN t2.收费类型='劳务费' THEN 金额 ELSE 0 END) AS '劳务费',

SUM(CASE WHEN t2.收费类型='工资费' THEN 金额 ELSE 0 END) AS '工资费'

FROM t1 INNER JOIN t2 ON T1.收费类型 = T2.收费类型

GROUP BY 

t2.单据号,CONVERT(VARCHAR(10),t2.日期 ,120) 

----------------结果----------------------------

/* 单据号   日期         管理费         劳务费         工资费

----- ---------- ----------- ----------- -----------

sk001 2015-11-15 100         0           0

sk002 2015-10-07 0           50          300

 

(2 行受影响)

*/