像写创建数据库与表时,按关键字及逗号进行换行,看起来很工整,以后在进行查看时方便,易懂!
如
-------------------------------------------------------
--
-- XXX数据库(对数据库进行说明)
--
-------------------------------------------------------
use master
go
if exists(select * from sysdatabases where name ='MyDB')
drop database MyDB
create database MyDB ON
(NAME='MyDB',
FILENAME ='C:\MSSQL\DATA\MyDB.MDF',
SIZE=5MB,
MAXSIZE=50MB)
LOG ON
(NAME='MyDB_LOG',
FILENAME ='C:\MSSQL\DATA\MyDB_LOG.lDF',
SIZE=5MB,
MAXSIZE=25MB)
declare @s varchar(8000)
set @s=''
select @s=@s+',['+cast(COL1 as varchar)+']=max(case b.COL1 when '+cast(COL1 as varchar)+' then cast(b.COL3 as varchar(10)) else '''' end)'
from TABLE2 group by COL1
exec('select a.COL1'+@s+'
from TABLE1 a
left join TABLE2 b on a.COL1=b.COL2
group by a.COL1')
create table table2(COL1 char(2),COL2 char(1),COL3 int)
insert into table2 select
'1','A',50 union all select
'4', 'A', 20 union all select
'3' , 'C', 50 union all select
'4' ,'B' , 75 union all select
'2' ,'B', 30 union all select
'1' , 'A' ,20
select a.COL2 as COL0,a.COL1,sum(a.COL3) as COL3 into #temp from table2 a
group by a.COL2,a.COL1
select * from #temp
select COL0 as COL2,
sum(case COL1 when '1' then COL3 else null end) as '1',
sum(case COL1 when '2' then COL3 else null end) as '2',
sum(case COL1 when '3' then COL3 else null end) as '3',
sum(case COL1 when '4' then COL3 else null end) as '4'
from #temp
group by COL0
drop table table2
drop table #temp
COL2 1 2 3 4
---- ----------- ----------- ----------- -----------
A 70 NULL NULL 20
B NULL 30 NULL 75
C NULL NULL 50 NULL
--示例数据
create table TABLE1(COL1 varchar(10))
insert TABLE1 select 'A'
union all select 'B'
union all select 'C'
create table TABLE2(COL1 int,COL2 varchar(10),COL3 int)
insert TABLE2 select 1,'A',50
union all select 4,'A',20
union all select 3,'C',50
union all select 4,'B',75
union all select 2,'B',30
union all select 1,'A',20
go
--查询
select a.COL1
,[1]=max(case b.COL1 when 1 then cast(b.COL3 as varchar(10)) else '' end)
,[2]=max(case b.COL1 when 2 then cast(b.COL3 as varchar(10)) else '' end)
,[3]=max(case b.COL1 when 3 then cast(b.COL3 as varchar(10)) else '' end)
,[4]=max(case b.COL1 when 4 then cast(b.COL3 as varchar(10)) else '' end)
from TABLE1 a
left join TABLE2 b on a.COL1=b.COL2
group by a.COL1
go
--删除测试
drop table TABLE1,TABLE2
/*--测试结果
COL1 1 2 3 4
---------- ---------- ---------- ---------- ----------
A 50 20
B 30 75
C 50