create table #T(pno varchar(20),areaname varchar(20),gtsale int,sesale int)
insert into #T select 'fe-001a','北京',7 ,8
insert into #T select 'fe-002a','南京',8 ,10
insert into #T select 'fe-001a','武汉',10,11declare @s varchar(8000)
set @s = 'select 货号=pno'
select @s = @s + ',['+areaname+'柜台]=sum(case '''+areaname+''' when areaname then gtsale end)'
+ ',['+areaname+'柜台]=sum(case '''+areaname+''' when areaname then sesale end)'
from
#T
group by
areanameset @s = @s + ' from #T group by pno order by pno'
exec(@s)
insert into #T select 'fe-001a','北京',7 ,8
insert into #T select 'fe-002a','南京',8 ,10
insert into #T select 'fe-001a','武汉',10,11declare @s varchar(8000)
set @s = 'select 货号=pno'
select @s = @s + ',['+areaname+'柜台]=sum(case '''+areaname+''' when areaname then gtsale end)'
+ ',['+areaname+'柜台]=sum(case '''+areaname+''' when areaname then sesale end)'
from
#T
group by
areanameset @s = @s + ' from #T group by pno order by pno'
exec(@s)
create table #T(pno varchar(20),areaname varchar(20),gtsale int,sesale int)
insert into #T select 'fe-001a','北京',7 ,8
insert into #T select 'fe-002a','南京',8 ,10
insert into #T select 'fe-001a','武汉',10,11
--执行交叉表查询
declare @s varchar(8000)
set @s = 'select 货号=pno'
select
@s = @s + ',['+areaname+'柜台]=sum(case '''+areaname+''' when areaname then gtsale end)'
+ ',['+areaname+'柜台]=sum(case '''+areaname+''' when areaname then sesale end)'
from
#T
group by
areanameset @s = @s + ' from #T group by pno order by pno'
exec(@s)--输出结果
/*
货号 北京柜台 北京批发 南京柜台 南京批发 武汉柜台 武汉批发
------- -------- -------- -------- -------- -------- --------
fe-001a 7 8 NULL NULL 10 11
fe-002a NULL NULL 8 10 NULL NULL
*/
create table tb(pno varchar(10),areaname varchar(10),gtsale int,sesale int)
insert into tb select 'fe-001a','北京',7 ,8
insert into tb select 'fe-002a','南京',8 ,10
insert into tb select 'fe-001a','武汉',10,11
go
declare @sql varchar(8000)
set @sql='select 货号=pno'
select @sql=@sql+',['+areaname+'(柜台 批发)]=max(case areaname when '''+areaname+
''' then cast(gtsale as char(5))+cast(sesale as varchar) else '''' end)' from tb group by areaname
set @sql=@sql+' from tb group by pno'
exec(@sql)
go
drop table tb
(
pno varchar(20),
areaname varchar(10),
gtsale int,
sesale int
)
insert A
select 'fe-001a','北京',7,8 union
select 'fe-002a','南京',8,10 union
select 'fe-001a','武汉',10,11--查询
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when areaname='''+areaname+''' then gtsale else null end) as [gtsale]'
+',max(case when areaname='''+areaname+''' then sesale else null end) as [sesale]'
from A group by areaname
select @sql='select pno'+@sql+' from A group by pno'
exec(@sql)--删除测试环境
drop table A--结果
/*
pno gtsale sesale gtsale sesale gtsale sesale
-------------------- ----------- ----------- ----------- ----------- ----------
fe-001a 7 8 NULL NULL 10 11
fe-002a NULL NULL 8 10 NULL NULL
*/
我应该怎么标示那一个是南京的柜台,怎么取北京 南京 武汉
货号 柜台 批发 柜台 批发 柜台 批发 ...
fe-001a 7 8 10 11 ...
fe-002a 8 10 ...
(
pno varchar(20),
areaname varchar(10),
gtsale int,
sesale int
)
insert A
select 'fe-001a','北京',7,8 union
select 'fe-002a','南京',8,10 union
select 'fe-001a','武汉',10,11--查询
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when areaname='''+areaname+''' then gtsale else null end) as ['+areaname+'柜台]'
+',max(case when areaname='''+areaname+''' then sesale else null end) as ['+areaname+'批发]'
from A group by areaname
select @sql='select pno'+@sql+' from A group by pno'
exec(@sql)--删除测试环境
drop table A--结果
/*pno 北京柜台 北京批发 南京柜台 南京批发 武汉柜台 武汉批发
-------------------- ----------- ----------- ----------- ----------- ----------- -----------
fe-001a 7 8 NULL NULL 10 11
fe-002a NULL NULL 8 10 NULL NULL
*/
(pno varchar(20),areaname varchar(200),gt int,se int)insert a
select 'fe-001a','北京',7,8 [union] all
select 'fe-002a','南京',8,10 [union] all
select 'fe-003a','武汉',10,11declare @sql varchar(8000)
set @sql =''
select @sql=@sql+',['+areaname+'-柜台]=max(case when areaname='''+areaname+''' then gt else '''' end)
,['+areaname+'-批发]=max(case when areaname='''+areaname+''' then se else '''' end)'
from a
group by areanameexec('select pno'+@sql+' from a group by pno')
godrop table a
pno 北京-柜台 北京-批发 南京-柜台 南京-批发 武汉-柜台 武汉-批发
-------------------- ----------- ----------- ----------- ----------- ----------- -----------
fe-001a 7 8 0 0 0 0
fe-002a 0 0 8 10 0 0
fe-003a 0 0 0 0 10 11
北京、上海之类的信息已经在单表头里体现。
create table #T(pno varchar(20),areaname varchar(20),gtsale int,sesale int)
insert into #T select 'fe-001a','北京',7 ,8
insert into #T select 'fe-002a','南京',8 ,10
insert into #T select 'fe-001a','武汉',10,11
--执行交叉表查询
declare @s varchar(8000)
set @s = 'select 货号=pno'
select
@s = @s + ',['+areaname+'合计]=sum(case '''+areaname+''' when areaname then gtsale+sesale end)'
+ ',['+areaname+'柜台]=sum(case '''+areaname+''' when areaname then gtsale end)'
+ ',['+areaname+'柜台]=sum(case '''+areaname+''' when areaname then sesale end)'
from
#T
group by
areanameset @s = @s + ' from #T group by pno order by pno'
exec(@s)--输出结果
/*
货号 北京合计 北京柜台 北京批发 南京合计 南京柜台 南京批发 武汉合计 武汉柜台 武汉批发
------- -------- -------- -------- -------- -------- -------- -------- -------- --------
fe-001a 15 7 8 NULL NULL NULL 21 10 11
fe-002a NULL NULL NULL 18 8 10 NULL NULL NULL
*/
-------------------- ----------- ----------- ----------- ----------- ----------- -----------
fe-001a ? ? 7 8 NULL NULL 10 11
fe-002a ? ? NULL NULL 8 10 NULL NULL
你可以网上搜索一下
比如如果使用.net,搜索"datagrid 表头",非常多
libin_ftsafe(子陌红尘)
在SQL Server 2000里无法实现双表头的需求,只能按之前给出的交叉表查询方式返回单表头。
北京、上海之类的信息已经在单表头里体现。
----------------------------------------Sql的双表头还是可以实现!!
我用Union 加上去,应该的行沙!!!
------- -------- -------- -------- -------- -------- --------
fe-001a 7 8 NULL NULL 10 11
fe-002a NULL NULL 8 10 NULL NULL然后再客户端处理一下,也能蒙混过去!!