temp1表
如果danhao为null的话,也要显示出来,显示出的leibie顺序一定要按编号顺序由左到右从小到大排列,帮忙看一下bh leibie danwei ypid shuliang riqi danhao
010201 铁板0.8 NULL NULL .0000 NULL NULL
010202 铁板1.4 NULL NULL .0000 NULL NULL
010203 大边框 NULL NULL .0000 NULL NULL
010204 暗插销 NULL NULL .0000 NULL NULL
010205 粉末 NULL NULL .0000 NULL NULL
010206 合页 NULL NULL .0000 NULL NULL declare @str varchar(4000)
set @str=''
select @str=@str+','+leibie+
'=sum(case when leibie='+QUOTENAME(leibie,'''')+' then shuliang else 0 end)'
from temp1
group by leibie
print @str
set @str='select danhao '+@str+' from temp1 group by danhao'
print @str
exec(@str)正常bianhao为null显示的应该也是这样的
bianhao 铁板0.8 铁板1.4 大边框 暗插销 .. 胶水
如果danhao为null的话,也要显示出来,显示出的leibie顺序一定要按编号顺序由左到右从小到大排列,帮忙看一下bh leibie danwei ypid shuliang riqi danhao
010201 铁板0.8 NULL NULL .0000 NULL NULL
010202 铁板1.4 NULL NULL .0000 NULL NULL
010203 大边框 NULL NULL .0000 NULL NULL
010204 暗插销 NULL NULL .0000 NULL NULL
010205 粉末 NULL NULL .0000 NULL NULL
010206 合页 NULL NULL .0000 NULL NULL declare @str varchar(4000)
set @str=''
select @str=@str+','+leibie+
'=sum(case when leibie='+QUOTENAME(leibie,'''')+' then shuliang else 0 end)'
from temp1
group by leibie
print @str
set @str='select danhao '+@str+' from temp1 group by danhao'
print @str
exec(@str)正常bianhao为null显示的应该也是这样的
bianhao 铁板0.8 铁板1.4 大边框 暗插销 .. 胶水
set @str=''
select @str=@str+','+leibie+
'=sum(case when leibie='+QUOTENAME(leibie,'''')+' then shuliang else 0 end)'
from temp1
group by leibie
order by min(bh)
print @str
set @str='select danhao '+@str+' from temp1 group by danhao'
print @str
exec(@str)
010201 铁板0.8 .0000 NULL
010202 铁板1.4 .0000 NULL
010203 大边框 .0000 NULL
010204 暗插销 .0000 NULL
010205 粉末 .0000 NULL
010206 合页 .0000 NULLdanhao 铁板0.8 铁板1.4 大边框 .. 合页
/*
temp1表
如果danhao为null的话,也要显示出来,显示出的leibie顺序一定要
按编号顺序由左到右从小到大排列,帮忙看一下bh leibie danwei ypid shuliang riqi danhao
010201 铁板0.8 NULL NULL .0000 NULL NULL
010202 铁板1.4 NULL NULL .0000 NULL NULL
010203 大边框 NULL NULL .0000 NULL NULL
010204 暗插销 NULL NULL .0000 NULL NULL
010205 粉末 NULL NULL .0000 NULL NULL
010206 合页 NULL NULL .0000 NULL NULL declare @str varchar(4000)
set @str=''
select @str=@str+','+leibie+
'=sum(case when leibie='+QUOTENAME(leibie,'''')+' then shuliang else 0 end)'
from temp1
group by leibie
print @str
set @str='select danhao '+@str+' from temp1 group by danhao'
print @str
exec(@str)正常bianhao为null显示的应该也是这样的
bianhao 铁板0.8 铁板1.4 大边框 暗插销 .. 胶水*/GO
IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
bh VARCHAR(6),
leibie VARCHAR(10),
danwei VARCHAR(4),
ypid VARCHAR(5),
shuliang INT,
riqi DATETIME,
danhao VARCHAR(5)
)
GO
INSERT TBL(bh,leibie,shuliang)
SELECT '010201','铁板0.8',0 UNION ALL
SELECT '','铁板1.4',0 UNION ALL
SELECT '010203','大边框',0 UNION ALL
SELECT '','暗插销',0 UNION ALL
SELECT '010205','粉末',0 UNION ALL
SELECT '010206','合页',0
DECLARE @STR VARCHAR(2000)
SET @STR=''
SELECT @STR=@STR+','+QUOTENAME(leibie,'[]')+'=MAX(CASE WHEN leibie='+QUOTENAME(leibie,'''')
+' THEN shuliang END)' FROM TBL
GROUP BY leibie
SET @STR='SELECT ISNULL(bh,'''') AS bianhao'+@STR+' FROM TBL GROUP BY bh order by min(bh)'
PRINT @STR
EXEC(@STR)不晓得你的结果究竟要什么样子
go
create table [temp1]([bh] varchar(6),[leibie] varchar(7),[shuliang] numeric(5,4),[danhao] varchar(5))
insert [temp1]
select '010201','铁板0.8',0.0000,null union all
select '010202','铁板1.4',.0000,null union all
select '010203','大边框',.0000,null union all
select '010204','暗插销',.0000,null union all
select '010205','粉末',.0000,null union all
select '010206','合页',.0000,null
godeclare @str varchar(4000)
set @str=''
select @str=@str+',['+leibie+
']=sum(case when leibie='+QUOTENAME(leibie,'''')+' then shuliang else 0 end)'
from temp1
group by leibie
order by min(bh)
--print @str
set @str='select danhao '+@str+' from temp1 group by danhao'
print @str
exec(@str)/**
danhao 铁板0.8 铁板1.4 大边框 暗插销 粉末 合页
------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
NULL 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000(1 行受影响)
**/
IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
bh VARCHAR(6),
leibie VARCHAR(10),
danwei VARCHAR(4),
ypid VARCHAR(5),
shuliang INT,
riqi DATETIME,
danhao VARCHAR(5)
)
GO
INSERT TBL(bh,leibie,shuliang)
SELECT '010201','铁板0.8',0 UNION ALL
SELECT '','铁板1.4',0 UNION ALL
SELECT '010203','大边框',0 UNION ALL
SELECT '','暗插销',0 UNION ALL
SELECT '010205','粉末',0 UNION ALL
SELECT '010206','合页',0
DECLARE @STR VARCHAR(2000)
SET @STR=''
SELECT @STR=@STR+','+QUOTENAME(leibie,'[]')+'=MAX(CASE WHEN leibie='+QUOTENAME(leibie,'''')
+' THEN shuliang ELSE 0 END)' FROM TBL
GROUP BY leibie
SET @STR='SELECT ISNULL(danhao,'''') AS bianhao'+@STR+' FROM TBL
GROUP BY danhao'
PRINT @STR
EXEC(@STR)
/*
bianhao 暗插销 大边框 粉末 合页 铁板0.8 铁板1.4
0 0 0 0 0 0
*/
这样行么
如表temp1数据如下面的,其中temp1 leibie有很多的,但要行转列后根据bh 010201,010202对应的leibie名顺序从左向右排,下面如果danhao为空,就把
010201 铁板0.8 NULL NULL 30 NULL A001
010202 铁板1.4 NULL NULL .0000 NULL NULL
010203 大边框 NULL NULL .0000 NULL NULL
010204 暗插销 NULL NULL .0000 NULL NULL
010205 粉末 NULL NULL .0000 NULL NULL
010206 合页 NULL NULL .0000 NULL NULL 结果为:
danhao 铁板0.8 铁板1.4 大边框 .. 合页
A001 30 0 0 0如果danhao都为null的话,就取不出来danhao了,就是下面数据位空的样式,只有行转列的表头了
danhao 铁板0.8 铁板1.4 大边框 .. 合页
这个单号要显示出来,使用了聚合函数,那么单号就必须在group by子句中,都为null的他就会当成一个情况处理,你这个需求,有点伤人 啊
danhao 铁板0.8 铁板1.4 大边框 .. 合页
A001 30 0 0 .. 0
如果shuliang 有值,danhao有值,就提示 列名无效了,shuliang也不能根据根据leibie,danhao聚合求和了