if not object_id(N'Tempdb..#A') is null drop table #A Go Create table #A([大类] nvarchar(2),[类别] nvarchar(2),[购买数] int,[退还数] int,[实售数] int) Insert #A select N'上身',N'外套',7,1,6 union all select N'下身',N'单裙',8,3,5 Go Select [类别1] as 类别, max(case when [大类]=N'上身' and [类别]=N'外套' then VALUE end) as [上身外套], max(case when [大类]=N'下身' and [类别]=N'单裙' then VALUE end) as [下身单裙] from #A AS a UNPIVOT ( VALUE FOR [类别1]IN(购买数,退还数,实售数) ) AS b group by [类别1]/* 类别 上身外套 下身单裙 退还数 1 3 实售数 6 5 购买数 7 8 */
create table tb(大类 varchar(10),类别 varchar(10),购买数 int,退还数 int,实售数 int) insert into tb select '上身','外套', 7, 1, 6 insert into tb select '下身','单裙', 8, 3, 5 go select * into tb1 from( select 大类,'类别' x, 类别 y from tb union all select 大类,'购买数',ltrim(购买数) from tb union all select 大类,'退还数',ltrim(退还数) from tb union all select 大类,'实售数',ltrim(实售数) from tb )t declare @s nvarchar(4000) select @s=isnull(@s+',','')+'['+ 大类 +']' from( select distinct 大类 from tb1 )t exec('select [x],'+@s+' from tb1 pivot (max([y]) for 大类 in('+@s+'))b') /* x 上身 下身 ------ ------------ ------------ 购买数 7 8 类别 外套 单裙 实售数 6 5 退还数 1 3(4 行受影响)*/ go drop table tb,tb1
还没人给出正确答案,请用pivot或unpivot来转,不要用case
请自己百度pivot或unpivot用法,自己转
這樣?use Tempdb go --> -->
if not object_id(N'Tempdb..#A') is null drop table #A Go Create table #A([大类] nvarchar(2),[类别] nvarchar(2),[购买数] int,[退还数] int,[实售数] int) Insert #A select N'上身',N'外套',7,1,6 union all select N'下身',N'单裙',8,3,5 Go with a as ( Select [类别1] as 类别,VALUE,[大类]+[类别] as Typefrom #A AS a UNPIVOT ( VALUE FOR [类别1]IN(购买数,退还数,实售数) ) AS b ) select * from a pivot (max(Value) for Type in([上身外套],[下身单裙])) as b/* 类别 上身外套 下身单裙 退还数 1 3 实售数 6 5 购买数 7 8 */
楼上的,我每次到这里发贴前都不知百度和google多少回
動態這樣轉 ,在你這環境用pivot不實用use Tempdb go --> -->
if not object_id(N'A') is null drop table A Go Create table A([大类] nvarchar(2),[类别] nvarchar(2),[代码] nvarchar(1),[购买数] int,[退还数] int,[实售数] int) Insert A select N'上身',N'外套',N'A',7,1,6 union all select N'下身',N'单裙',N'B',8,3,5 Go declare @s nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000),@s4 nvarchar(4000) select @s=isnull(@s+',','declare ')+'@'+rtrim(Colid)+' nvarchar(4000)', @s2=isnull(@s2+',','select ')+'@'+rtrim(Colid)+'=N'''+case when @s2 is not null then 'union all select' else ' select ' end+N' [大类]=N'''+quotename(Name,'''')+'''''', @s3=isnull(@s3,'')+'select @'+rtrim(Colid)+'=@'+rtrim(Colid)+N'+'',''+quotename([大类])+''=N''+quotename('+quotename(Name)+','''''''') from A ', @s4=isnull(@s4+'+','')+'@'+rtrim(Colid) from syscolumns where id=object_id('A') and Name not in(N'大类') --print @s+' '+@s2+' '+@s3+' exec('+@s4+')' --显示执行语句 exec(@s+' '+@s2+' '+@s3+' exec('+@s4+')') /* 大类 上身 下身 类别 外套 单裙 代码 A B 购买数 7 8 退还数 1 3 实售数 6 5 */
本帖最后由 roy_88 于 2011-10-25 12:48:37 编辑
如果多插入一条记录 insert into tb select '上身','衬衣', 8, 1, 7 你这样做是不是就不行了,我试了,衬衣不显示出来,求解答.
如果再插入一行 select N'上身',N'衬衣',N'B',8,3,5 就挂了
use Tempdb go --> -->
if not object_id(N'A') is null drop table A Go Create table A([大类] nvarchar(2),[类别] nvarchar(2),[代码] nvarchar(1),[购买数] int,[退还数] int,[实售数] int) Insert A select N'上身',N'外套',N'A',7,1,6 union all select N'下身',N'单裙',N'B',8,3,5 UNION ALL select N'上身',N'衬衣',N'B',8,3,5 Go declare @s nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000),@s4 nvarchar(4000) select @s=isnull(@s+',','declare ')+'@'+rtrim(Colid)+' nvarchar(4000)', @s2=isnull(@s2+',','select ')+'@'+rtrim(Colid)+'=N'''+case when @s2 is not null then 'union all select' else ' select ' end+N' [大类]=N'''+quotename(Name,'''')+'''''', @s3=isnull(@s3,'')+'select @'+rtrim(Colid)+'=@'+rtrim(Colid)+N'+'',''+quotename([大类])+''=N''+quotename('+quotename(Name)+','''''''') from A ', @s4=isnull(@s4+'+','')+'@'+rtrim(Colid) from syscolumns where id=object_id('A') and Name not in(N'大类') --print @s+' '+@s2+' '+@s3+' exec('+@s4+')' --显示执行语句 exec(@s+' '+@s2+' '+@s3+' exec('+@s4+')') /* 大类 上身 下身 上身 类别 外套 单裙 衬衣 代码 A B B 购买数 7 8 8 退还数 1 3 3 实售数 6 5 5 */不是這樣結果麼?
---测试一下邹老大的代码---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2011-10-25 15:53:18 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation -- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64) -- ---------------------------------------------------------------- --> 测试数据:[A] if object_id('[A]') is not null drop table [A] go create table [A]([大类] varchar(4),[类别] varchar(4),[代码] varchar(1),[购买数] int,[退还数] int,[实售数] int) insert [A] select '上身','外套','A',7,1,6 union all select '下身','单裙','B',8,3,5 union all select '上身','衬衣',N'B',8,3,5--------------开始查询-------------------------- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_zj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[p_zj] GO /*--行列互换的通用存储过程(原著:邹建):将指定的表,按指定的字段进行行列互换*/create proc p_zj @tbname sysname, --要处理的表名 @fdname sysname, --做为转换的列名 @new_fdname sysname='' --为转换后的列指定列名 as declare @s1 varchar(8000) , @s2 varchar(8000), @s3 varchar(8000) , @s4 varchar(8000), @s5 varchar(8000) , @i varchar(10) select @s1 = '' , @s2 = '' , @s3 = '' , @s4 = '' , @s5 = '' , @i = '0' select @s1 = @s1 + ',@' + @i + ' varchar(8000)', @s2 = @s2 + ',@' + @i + '=''' + case isnull(@new_fdname , '') when '' then '' else @new_fdname + '=' end + '''''' + name + '''''''', @s3 = @s3 + 'select @' + @i + '=@' + @i + '+'',['' + [' + @fdname + ']+'']=''+quotename('+quotename(Name)+','''''''') from [' + @tbname + ']', @s4 = @s4 + ',@' + @i + '=''select ''+@' + @i, @s5 = @s5 + '+'' union all ''+@' + @i, @i=cast(@i as int)+1 from syscolumns where object_id(@tbname)=id and name<>@fdnameselect @s1=substring(@s1,2,8000), @s2=substring(@s2,2,8000), @s4=substring(@s4,2,8000), @s5=substring(@s5,16,8000) exec('declare ' + @s1 + 'select ' + @s2 + @s3 + 'select ' + @s4 + ' exec(' + @s5 + ')') go exec p_zj 'A', '大类' , 'col' ----------------结果---------------------------- /* col 上身 下身 上身 ------ ---- ---- ---- 类别 外套 单裙 衬衣 代码 A B B 购买数 7 8 8 退还数 1 3 3 实售数 6 5 5(5 行受影响) */
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2011-10-25 11:53:18 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation -- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64) -- ---------------------------------------------------------------- --> 测试数据:[A] if object_id('[A]') is not null drop table [A] go create table [A]([大类] varchar(4),[类别] varchar(4),[代码] varchar(1),[购买数] int,[退还数] int,[实售数] int) insert [A] select '上身','外套','A',7,1,6 union all select '下身','单裙','B',8,3,5 union all select '上身','衬衣',N'B',8,3,5--------------开始查询-------------------------- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_zj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[p_zj] GO /*--行列互换的通用存储过程(原著:邹建):将指定的表,按指定的字段进行行列互换*/create proc p_zj @tbname sysname, --要处理的表名 @fdname sysname, --做为转换的列名 @new_fdname sysname='' --为转换后的列指定列名 as declare @s1 varchar(8000) , @s2 varchar(8000), @s3 varchar(8000) , @s4 varchar(8000), @s5 varchar(8000) , @i varchar(10) select @s1 = '' , @s2 = '' , @s3 = '' , @s4 = '' , @s5 = '' , @i = '0' select @s1 = @s1 + ',@' + @i + ' varchar(8000)', @s2 = @s2 + ',@' + @i + '=''' + case isnull(@new_fdname , '') when '' then '' else @new_fdname + '=' end + '''''' + name + '''''''', @s3 = @s3 + 'select @' + @i + '=@' + @i + '+'',['' + [' + @fdname + ']+'']=''+quotename('+quotename(Name)+','''''''') from [' + @tbname + ']', @s4 = @s4 + ',@' + @i + '=''select ''+@' + @i, @s5 = @s5 + '+'' union all ''+@' + @i, @i=cast(@i as int)+1 from syscolumns where object_id(@tbname)=id and name<>@fdnameselect @s1=substring(@s1,2,8000), @s2=substring(@s2,2,8000), @s4=substring(@s4,2,8000), @s5=substring(@s5,16,8000) exec('declare ' + @s1 + 'select ' + @s2 + @s3 + 'select ' + @s4 + ' exec(' + @s5 + ')') go exec p_zj 'A', '大类' , 'col' ----------------结果---------------------------- /* col 上身 下身 上身 ------ ---- ---- ---- 类别 外套 单裙 衬衣 代码 A B B 购买数 7 8 8 退还数 1 3 3 实售数 6 5 5(5 行受影响) */
臨時表這樣改一下就行了 object_id('A') 改為 --object_id('Tempdb..#A')use Tempdb go --> -->
if not object_id(N'Tempdb..#A') is null drop table #A Go Create table #A([大类] nvarchar(2),[类别] nvarchar(2),[代码] nvarchar(1),[购买数] int,[退还数] int,[实售数] int) Insert #A select N'上身',N'外套',N'A',7,1,6 union all select N'下身',N'单裙',N'B',8,3,5 UNION ALL select N'上身',N'衬衣',N'B',8,3,5 Go declare @s nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000),@s4 nvarchar(4000) select @s=isnull(@s+',','declare ')+'@'+rtrim(Colid)+' nvarchar(4000)', @s2=isnull(@s2+',','select ')+'@'+rtrim(Colid)+'=N'''+case when @s2 is not null then 'union all select' else ' select ' end+N' [大类]=N'''+quotename(Name,'''')+'''''', @s3=isnull(@s3,'')+'select @'+rtrim(Colid)+'=@'+rtrim(Colid)+N'+'',''+quotename([大类])+''=N''+quotename('+quotename(Name)+','''''''') from A ', @s4=isnull(@s4+'+','')+'@'+rtrim(Colid) from syscolumns where id=object_id('Tempdb..#A') and Name not in(N'大类') --print @s+' '+@s2+' '+@s3+' exec('+@s4+')' --显示执行语句 exec(@s+' '+@s2+' '+@s3+' exec('+@s4+')') /* 大类 上身 下身 上身 类别 外套 单裙 衬衣 代码 A B B 购买数 7 8 8 退还数 1 3 3 实售数 6 5 5 */
USE [erp_product]GO /****** Object: StoredProcedure [dbo].[rep_ProductCategory] Script Date: 10/25/2011 09:34:48 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: liqb2 -- Create date: 2011-10-24 -- Description: 产品分析--大类 -- =============================================ALTER PROCEDURE [dbo].[rep_ProductCategory] @iodate datetime AS
BEGIN SET NOCOUNT ON;
if not object_id(N'Tempdb..#B') is null drop table #B; select kind.type as 大类 ,kind.name as 类别 ,odetail.kind as 代码 ,sum(case odetail.io when 'O' then odetail.qty else 0 end) as 购买件数 ,sum(case odetail.io when 'I' then odetail.qty else 0 end) as 退货件数 ,sum(case odetail.io when 'O' then odetail.qty else (-1) * odetail.qty end) as 实售件数 into #B from odetail inner style on odetail.style = style.code inner cardscript on odetail.card = cardscript.cnumber inner kind on odetail.kind = kind.code where odetail.kind in ('C','G','J','W','R','L','M','K','E','A','N','Y','P','Z','U','F','H','D','T') and odetail.iodate >= '2011-5-1' --开始日期 group by kind.type,kind.name,odetail.kind declare @s nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000),@s4 nvarchar(4000) select @s=isnull(@s+',','declare ')+'@'+rtrim(Colid)+' nvarchar(4000)', @s2=isnull(@s2+',','select ')+'@'+rtrim(Colid)+'=N'''+ case when @s2 is not null then 'union all select' else ' select ' end+N' [大类]=N'''+quotename(Name,'''')+'''''', @s3=isnull(@s3,'')+'select @'+rtrim(Colid)+'=@'+rtrim(Colid)+ N'+'',''+quotename([大类])+''=N''+quotename('+quotename(Name)+','''''''') from #B ', @s4=isnull(@s4+'+','')+'@'+rtrim(Colid) from syscolumns where id=object_id('#Tempdb..#B') and Name not in(N'大类') --print @s+' '+@s2+' '+@s3+' exec('+@s4+')' --显示执行语句 exec(@s+' '+@s2+' '+@s3+' exec('+@s4+')')
END /****** Object: StoredProcedure [dbo].[addDepartment] Script Date: 08/06/2010 15:30:23 ******/ SET ANSI_NULLS ON 不好意思啦,主要是这个USE [erp_product]有点问题,麻烦你
declare @s nvarchar(max),@s2 nvarchar(max),@s3 nvarchar(max),@s4 nvarchar(max) select @s=isnull(@s+',','declare ')+'@'+rtrim(Colid)+' nvarchar(max)', @s2=isnull(@s2+',','select ')+'@'+rtrim(Colid)+'=N'''+ case when @s2 is not null then 'union all select' else ' select ' end+N' [大类]=N'''+quotename(Name,'''')+'''''', @s3=isnull(@s3,'')+'select @'+rtrim(Colid)+'=@'+rtrim(Colid)+ N'+'',''+quotename([大类])+''=N''+quotename('+quotename(Name)+','''''''') from #B ', @s4=isnull(@s4+'+','')+'@'+rtrim(Colid) from syscolumns where id=object_id('#Tempdb..#B') and Name not in(N'大类') --print @s+' '+@s2+' '+@s3+' exec('+@s4+')' --显示执行语句 exec(@s+' '+@s2+' '+@s3+' exec('+@s4+')') 把變量改為nvarchar(max)再試試
如果我单独这样执行是没问题的 use tempdb if not object_id(N'Tempdb..#D') is null drop table #D select kind.type as 大类 ,kind.name as 类别 ,odetail.kind as 代码 ,sum(case odetail.io when 'O' then odetail.qty else 0 end) as 购买件数 ,sum(case odetail.io when 'I' then odetail.qty else 0 end) as 退货件数 ,sum(case odetail.io when 'O' then odetail.qty else (-1) * odetail.qty end) as 实售件数 into #D from jxcdb.dbo.odetail inner join jxcdb.dbo.style on odetail.style = style.code inner join jxcdb.dbo.cardscript on odetail.card = cardscript.cnumber inner join jxcdb.dbo.kind on odetail.kind = kind.code where odetail.kind in ('C','G','J','W','R','L','M','K','E','A','N','Y','P','Z','U','F','H','D','T') and odetail.iodate >= '2011-5-1' --开始日期 group by kind.type,kind.name,odetail.kind declare @s nvarchar(max),@s2 nvarchar(max),@s3 nvarchar(max),@s4 nvarchar(max) select @s=isnull(@s+',','declare ')+'@'+rtrim(Colid)+' nvarchar(max)', @s2=isnull(@s2+',','select ')+'@'+rtrim(Colid)+'=N'''+ case when @s2 is not null then 'union all select' else ' select ' end+N' [大类]=N'''+quotename(Name,'''')+'''''', @s3=isnull(@s3,'')+'select @'+rtrim(Colid)+'=@'+rtrim(Colid)+ N'+'',''+quotename([大类])+''=N''+quotename('+quotename(Name)+','''''''') from #D ', @s4=isnull(@s4+'+','')+'@'+rtrim(Colid) from syscolumns where id=object_id('Tempdb..#D') and Name not in(N'大类') print @s+' '+@s2+' '+@s3+' exec('+@s4+')' --显示执行语句 exec(@s+' '+@s2+' '+@s3+' exec('+@s4+')')
與是否存儲過程沒關係。估計是你的數據問題。 你的數據是否有字段為null
你查看一下select * from #B--臨時表的數據是否有null
from Tempdb..syscolumns --這里改改不好意思漏了這里,我測試的Tempdb--DB,所以就會報錯
没有为null的,那为什么我这样执行就没错呢 use tempdb if not object_id(N'Tempdb..#D') is null drop table #D select kind.type as 大类 ,kind.name as 类别 ,odetail.kind as 代码 ,sum(case odetail.io when 'O' then odetail.qty else 0 end) as 购买件数 ,sum(case odetail.io when 'I' then odetail.qty else 0 end) as 退货件数 ,sum(case odetail.io when 'O' then odetail.qty else (-1) * odetail.qty end) as 实售件数 into #D from jxcdb.dbo.odetail inner join jxcdb.dbo.style on odetail.style = style.code inner join jxcdb.dbo.cardscript on odetail.card = cardscript.cnumber inner join jxcdb.dbo.kind on odetail.kind = kind.code where odetail.kind in ('C','G','J','W','R','L','M','K','E','A','N','Y','P','Z','U','F','H','D','T') and odetail.iodate >= '2011-5-1' --开始日期 group by kind.type,kind.name,odetail.kind declare @s nvarchar(max),@s2 nvarchar(max),@s3 nvarchar(max),@s4 nvarchar(max) select @s=isnull(@s+',','declare ')+'@'+rtrim(Colid)+' nvarchar(max)', @s2=isnull(@s2+',','select ')+'@'+rtrim(Colid)+'=N'''+ case when @s2 is not null then 'union all select' else ' select ' end+N' [大类]=N'''+quotename(Name,'''')+'''''', @s3=isnull(@s3,'')+'select @'+rtrim(Colid)+'=@'+rtrim(Colid)+ N'+'',''+quotename([大类])+''=N''+quotename('+quotename(Name)+','''''''') from #D ', @s4=isnull(@s4+'+','')+'@'+rtrim(Colid) from syscolumns where id=object_id('Tempdb..#D') and Name not in(N'大类') print @s+' '+@s2+' '+@s3+' exec('+@s4+')' --显示执行语句 exec(@s+' '+@s2+' '+@s3+' exec('+@s4+')')
/*
数据库中tb表格如下
月份 工资 福利 奖金
1月 100 200 300
2月 110 210 310
3月 120 220 320
4月 130 230 330我想得到的结果是项目 1月 2月 3月 4月
工资 100 110 120 130
福利 200 210 220 230
奖金 300 310 320 330就是说完全把表格的行列颠倒,有点像那种旋转矩阵,请问如何用sql 语句实现?
*/if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[p_zj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_zj]
GO
/*--行列互换的通用存储过程(原著:邹建):将指定的表,按指定的字段进行行列互换*/create proc p_zj
@tbname sysname, --要处理的表名
@fdname sysname, --做为转换的列名
@new_fdname sysname='' --为转换后的列指定列名
as
declare @s1 varchar(8000) , @s2 varchar(8000),
@s3 varchar(8000) , @s4 varchar(8000),
@s5 varchar(8000) , @i varchar(10)
select @s1 = '' , @s2 = '' , @s3 = '' , @s4 = '' , @s5 = '' , @i = '0'
select @s1 = @s1 + ',@' + @i + ' varchar(8000)',
@s2 = @s2 + ',@' + @i + '=''' + case isnull(@new_fdname , '') when '' then ''
else @new_fdname + '=' end + '''''' + name + '''''''',
@s3 = @s3 + 'select @' + @i + '=@' + @i + '+'',['' + [' + @fdname +
']+'']=''+cast([' + name + '] as varchar) from [' + @tbname + ']',
@s4 = @s4 + ',@' + @i + '=''select ''+@' + @i,
@s5 = @s5 + '+'' union all ''+@' + @i,
@i=cast(@i as int)+1
from syscolumns
where object_id(@tbname)=id and name<>@fdnameselect @s1=substring(@s1,2,8000),
@s2=substring(@s2,2,8000),
@s4=substring(@s4,2,8000),
@s5=substring(@s5,16,8000)
exec('declare ' + @s1 + 'select ' + @s2 + @s3 + 'select ' + @s4 + '
exec(' + @s5 + ')')
go--用上面的存储过程测试:create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)
insert Test
select '1月',100,200,300 union all
select '2月',110,210,310 union all
select '3月',120,220,320 union all
select '4月',130,230,330
goexec p_zj 'Test', '月份' , '项目'drop table Test
drop proc p_zj/*
项目 1月 2月 3月 4月
---- ----------- ----------- ----------- -----------
福利 200 210 220 230
工资 100 110 120 130
奖金 300 310 320 330(所影响的行数为 3 行)
*//*
静态写法(SQL2005)
*/
--测试环境
create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)
insert Test
select '1月',100,200,300 union all
select '2月',110,210,310 union all
select '3月',120,220,320 union all
select '4月',130,230,330
go
--测试语句
SELECT * FROM
(
SELECT 考核月份,月份,金额 FROM
(SELECT 月份, 工资, 福利, 奖金 FROM Test) p
UNPIVOT
(金额 FOR 考核月份 IN (工资, 福利, 奖金))AS unpvt
) T
PIVOT
(MAX(金额) FOR 月份 in ([1月],[2月],[3月],[4月]))AS pt--测试结果/*
考核月份 1月 2月 3月 4月
------- ----- ----- ------ -------
福利200210220230
工资100110120130
奖金300310320330
*/--删除环境
Drop table Test
http://blog.csdn.net/qianjin036a/article/details/6582237
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([大类] nvarchar(2),[类别] nvarchar(2),[购买数] int,[退还数] int,[实售数] int)
Insert #A
select N'上身',N'外套',7,1,6 union all
select N'下身',N'单裙',8,3,5
Go
Select
[类别1] as 类别,
max(case when [大类]=N'上身' and [类别]=N'外套' then VALUE end) as [上身外套],
max(case when [大类]=N'下身' and [类别]=N'单裙' then VALUE end) as [下身单裙]
from
#A AS a
UNPIVOT
(
VALUE FOR [类别1]IN(购买数,退还数,实售数)
) AS b
group by [类别1]/*
类别 上身外套 下身单裙
退还数 1 3
实售数 6 5
购买数 7 8
*/
insert into tb select '上身','外套', 7, 1, 6
insert into tb select '下身','单裙', 8, 3, 5
go
select * into tb1 from(
select 大类,'类别' x, 类别 y from tb
union all
select 大类,'购买数',ltrim(购买数) from tb
union all
select 大类,'退还数',ltrim(退还数) from tb
union all
select 大类,'实售数',ltrim(实售数) from tb
)t
declare @s nvarchar(4000)
select @s=isnull(@s+',','')+'['+ 大类 +']' from(
select distinct 大类 from tb1
)t
exec('select [x],'+@s+' from tb1 pivot (max([y]) for 大类 in('+@s+'))b')
/*
x 上身 下身
------ ------------ ------------
购买数 7 8
类别 外套 单裙
实售数 6 5
退还数 1 3(4 行受影响)*/
go
drop table tb,tb1
這樣?use Tempdb
go
--> -->
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([大类] nvarchar(2),[类别] nvarchar(2),[购买数] int,[退还数] int,[实售数] int)
Insert #A
select N'上身',N'外套',7,1,6 union all
select N'下身',N'单裙',8,3,5
Go
with a
as
(
Select
[类别1] as 类别,VALUE,[大类]+[类别] as Typefrom
#A AS a
UNPIVOT
(
VALUE FOR [类别1]IN(购买数,退还数,实售数)
) AS b
)
select * from a pivot (max(Value) for Type in([上身外套],[下身单裙])) as b/*
类别 上身外套 下身单裙
退还数 1 3
实售数 6 5
购买数 7 8
*/
動態這樣轉 ,在你這環境用pivot不實用use Tempdb
go
--> -->
if not object_id(N'A') is null
drop table A
Go
Create table A([大类] nvarchar(2),[类别] nvarchar(2),[代码] nvarchar(1),[购买数] int,[退还数] int,[实售数] int)
Insert A
select N'上身',N'外套',N'A',7,1,6 union all
select N'下身',N'单裙',N'B',8,3,5
Go
declare @s nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000),@s4 nvarchar(4000)
select
@s=isnull(@s+',','declare ')+'@'+rtrim(Colid)+' nvarchar(4000)',
@s2=isnull(@s2+',','select ')+'@'+rtrim(Colid)+'=N'''+case when @s2 is not null then 'union all select' else ' select ' end+N' [大类]=N'''+quotename(Name,'''')+'''''',
@s3=isnull(@s3,'')+'select @'+rtrim(Colid)+'=@'+rtrim(Colid)+N'+'',''+quotename([大类])+''=N''+quotename('+quotename(Name)+','''''''') from A ',
@s4=isnull(@s4+'+','')+'@'+rtrim(Colid)
from
syscolumns
where
id=object_id('A') and Name not in(N'大类')
--print @s+' '+@s2+' '+@s3+' exec('+@s4+')' --显示执行语句
exec(@s+' '+@s2+' '+@s3+' exec('+@s4+')') /*
大类 上身 下身
类别 外套 单裙
代码 A B
购买数 7 8
退还数 1 3
实售数 6 5
*/
如果多插入一条记录
insert into tb select '上身','衬衣', 8, 1, 7
你这样做是不是就不行了,我试了,衬衣不显示出来,求解答.
select N'上身',N'衬衣',N'B',8,3,5
就挂了
use Tempdb
go
--> -->
if not object_id(N'A') is null
drop table A
Go
Create table A([大类] nvarchar(2),[类别] nvarchar(2),[代码] nvarchar(1),[购买数] int,[退还数] int,[实售数] int)
Insert A
select N'上身',N'外套',N'A',7,1,6 union all
select N'下身',N'单裙',N'B',8,3,5 UNION ALL
select N'上身',N'衬衣',N'B',8,3,5
Go
declare @s nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000),@s4 nvarchar(4000)
select
@s=isnull(@s+',','declare ')+'@'+rtrim(Colid)+' nvarchar(4000)',
@s2=isnull(@s2+',','select ')+'@'+rtrim(Colid)+'=N'''+case when @s2 is not null then 'union all select' else ' select ' end+N' [大类]=N'''+quotename(Name,'''')+'''''',
@s3=isnull(@s3,'')+'select @'+rtrim(Colid)+'=@'+rtrim(Colid)+N'+'',''+quotename([大类])+''=N''+quotename('+quotename(Name)+','''''''') from A ',
@s4=isnull(@s4+'+','')+'@'+rtrim(Colid)
from
syscolumns
where
id=object_id('A') and Name not in(N'大类')
--print @s+' '+@s2+' '+@s3+' exec('+@s4+')' --显示执行语句
exec(@s+' '+@s2+' '+@s3+' exec('+@s4+')')
/*
大类 上身 下身 上身
类别 外套 单裙 衬衣
代码 A B B
购买数 7 8 8
退还数 1 3 3
实售数 6 5 5
*/不是這樣結果麼?
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-10-25 15:53:18
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([大类] varchar(4),[类别] varchar(4),[代码] varchar(1),[购买数] int,[退还数] int,[实售数] int)
insert [A]
select '上身','外套','A',7,1,6 union all
select '下身','单裙','B',8,3,5 union all
select '上身','衬衣',N'B',8,3,5--------------开始查询--------------------------
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[p_zj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_zj]
GO
/*--行列互换的通用存储过程(原著:邹建):将指定的表,按指定的字段进行行列互换*/create proc p_zj
@tbname sysname, --要处理的表名
@fdname sysname, --做为转换的列名
@new_fdname sysname='' --为转换后的列指定列名
as
declare @s1 varchar(8000) , @s2 varchar(8000),
@s3 varchar(8000) , @s4 varchar(8000),
@s5 varchar(8000) , @i varchar(10)
select @s1 = '' , @s2 = '' , @s3 = '' , @s4 = '' , @s5 = '' , @i = '0'
select @s1 = @s1 + ',@' + @i + ' varchar(8000)',
@s2 = @s2 + ',@' + @i + '=''' + case isnull(@new_fdname , '') when '' then ''
else @new_fdname + '=' end + '''''' + name + '''''''',
@s3 = @s3 + 'select @' + @i + '=@' + @i + '+'',['' + [' + @fdname +
']+'']=''+quotename('+quotename(Name)+','''''''') from [' + @tbname + ']',
@s4 = @s4 + ',@' + @i + '=''select ''+@' + @i,
@s5 = @s5 + '+'' union all ''+@' + @i,
@i=cast(@i as int)+1
from syscolumns
where object_id(@tbname)=id and name<>@fdnameselect @s1=substring(@s1,2,8000),
@s2=substring(@s2,2,8000),
@s4=substring(@s4,2,8000),
@s5=substring(@s5,16,8000)
exec('declare ' + @s1 + 'select ' + @s2 + @s3 + 'select ' + @s4 + '
exec(' + @s5 + ')')
go
exec p_zj 'A', '大类' , 'col'
----------------结果----------------------------
/* col 上身 下身 上身
------ ---- ---- ----
类别 外套 单裙 衬衣
代码 A B B
购买数 7 8 8
退还数 1 3 3
实售数 6 5 5(5 行受影响)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-10-25 11:53:18
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([大类] varchar(4),[类别] varchar(4),[代码] varchar(1),[购买数] int,[退还数] int,[实售数] int)
insert [A]
select '上身','外套','A',7,1,6 union all
select '下身','单裙','B',8,3,5 union all
select '上身','衬衣',N'B',8,3,5--------------开始查询--------------------------
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[p_zj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_zj]
GO
/*--行列互换的通用存储过程(原著:邹建):将指定的表,按指定的字段进行行列互换*/create proc p_zj
@tbname sysname, --要处理的表名
@fdname sysname, --做为转换的列名
@new_fdname sysname='' --为转换后的列指定列名
as
declare @s1 varchar(8000) , @s2 varchar(8000),
@s3 varchar(8000) , @s4 varchar(8000),
@s5 varchar(8000) , @i varchar(10)
select @s1 = '' , @s2 = '' , @s3 = '' , @s4 = '' , @s5 = '' , @i = '0'
select @s1 = @s1 + ',@' + @i + ' varchar(8000)',
@s2 = @s2 + ',@' + @i + '=''' + case isnull(@new_fdname , '') when '' then ''
else @new_fdname + '=' end + '''''' + name + '''''''',
@s3 = @s3 + 'select @' + @i + '=@' + @i + '+'',['' + [' + @fdname +
']+'']=''+quotename('+quotename(Name)+','''''''') from [' + @tbname + ']',
@s4 = @s4 + ',@' + @i + '=''select ''+@' + @i,
@s5 = @s5 + '+'' union all ''+@' + @i,
@i=cast(@i as int)+1
from syscolumns
where object_id(@tbname)=id and name<>@fdnameselect @s1=substring(@s1,2,8000),
@s2=substring(@s2,2,8000),
@s4=substring(@s4,2,8000),
@s5=substring(@s5,16,8000)
exec('declare ' + @s1 + 'select ' + @s2 + @s3 + 'select ' + @s4 + '
exec(' + @s5 + ')')
go
exec p_zj 'A', '大类' , 'col'
----------------结果----------------------------
/* col 上身 下身 上身
------ ---- ---- ----
类别 外套 单裙 衬衣
代码 A B B
购买数 7 8 8
退还数 1 3 3
实售数 6 5 5(5 行受影响)
*/
这个过程我不需要创建表,我把数据放在一个临时表#Product,是不是把你的A换成#Product就可以了,我试了不行,不知该怎样,麻烦你了
object_id('A')
改為
--object_id('Tempdb..#A')use Tempdb
go
--> -->
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([大类] nvarchar(2),[类别] nvarchar(2),[代码] nvarchar(1),[购买数] int,[退还数] int,[实售数] int)
Insert #A
select N'上身',N'外套',N'A',7,1,6 union all
select N'下身',N'单裙',N'B',8,3,5 UNION ALL
select N'上身',N'衬衣',N'B',8,3,5
Go
declare @s nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000),@s4 nvarchar(4000)
select
@s=isnull(@s+',','declare ')+'@'+rtrim(Colid)+' nvarchar(4000)',
@s2=isnull(@s2+',','select ')+'@'+rtrim(Colid)+'=N'''+case when @s2 is not null then 'union all select' else ' select ' end+N' [大类]=N'''+quotename(Name,'''')+'''''',
@s3=isnull(@s3,'')+'select @'+rtrim(Colid)+'=@'+rtrim(Colid)+N'+'',''+quotename([大类])+''=N''+quotename('+quotename(Name)+','''''''') from A ',
@s4=isnull(@s4+'+','')+'@'+rtrim(Colid)
from
syscolumns
where
id=object_id('Tempdb..#A') and Name not in(N'大类')
--print @s+' '+@s2+' '+@s3+' exec('+@s4+')' --显示执行语句
exec(@s+' '+@s2+' '+@s3+' exec('+@s4+')') /*
大类 上身 下身 上身
类别 外套 单裙 衬衣
代码 A B B
购买数 7 8 8
退还数 1 3 3
实售数 6 5 5
*/
也不行吧,我自己的数据库就用不上了
object_id('A')
改為
--object_id('Tempdb..#A')
/****** Object: StoredProcedure [dbo].[rep_ProductCategory] Script Date: 10/25/2011 09:34:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: liqb2
-- Create date: 2011-10-24
-- Description: 产品分析--大类
-- =============================================ALTER PROCEDURE [dbo].[rep_ProductCategory]
@iodate datetime
AS
BEGIN
SET NOCOUNT ON;
if not object_id(N'Tempdb..#B') is null
drop table #B; select kind.type as 大类
,kind.name as 类别
,odetail.kind as 代码
,sum(case odetail.io when 'O' then odetail.qty else 0 end) as 购买件数
,sum(case odetail.io when 'I' then odetail.qty else 0 end) as 退货件数
,sum(case odetail.io when 'O' then odetail.qty else (-1) * odetail.qty end) as 实售件数
into #B
from odetail
inner style on odetail.style = style.code
inner cardscript on odetail.card = cardscript.cnumber
inner kind on odetail.kind = kind.code
where odetail.kind in ('C','G','J','W','R','L','M','K','E','A','N','Y','P','Z','U','F','H','D','T')
and odetail.iodate >= '2011-5-1' --开始日期
group by kind.type,kind.name,odetail.kind
declare @s nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000),@s4 nvarchar(4000)
select
@s=isnull(@s+',','declare ')+'@'+rtrim(Colid)+' nvarchar(4000)',
@s2=isnull(@s2+',','select ')+'@'+rtrim(Colid)+'=N'''+
case when @s2 is not null then 'union all select' else ' select ' end+N' [大类]=N'''+quotename(Name,'''')+'''''',
@s3=isnull(@s3,'')+'select @'+rtrim(Colid)+'=@'+rtrim(Colid)+
N'+'',''+quotename([大类])+''=N''+quotename('+quotename(Name)+','''''''') from #B ',
@s4=isnull(@s4+'+','')+'@'+rtrim(Colid)
from
syscolumns
where
id=object_id('#Tempdb..#B') and Name not in(N'大类')
--print @s+' '+@s2+' '+@s3+' exec('+@s4+')' --显示执行语句
exec(@s+' '+@s2+' '+@s3+' exec('+@s4+')')
END
/****** Object: StoredProcedure [dbo].[addDepartment] Script Date: 08/06/2010 15:30:23 ******/
SET ANSI_NULLS ON
不好意思啦,主要是这个USE [erp_product]有点问题,麻烦你
超過時把,4000改為max
select
@s=isnull(@s+',','declare ')+'@'+rtrim(Colid)+' nvarchar(max)',
@s2=isnull(@s2+',','select ')+'@'+rtrim(Colid)+'=N'''+
case when @s2 is not null then 'union all select' else ' select ' end+N' [大类]=N'''+quotename(Name,'''')+'''''',
@s3=isnull(@s3,'')+'select @'+rtrim(Colid)+'=@'+rtrim(Colid)+
N'+'',''+quotename([大类])+''=N''+quotename('+quotename(Name)+','''''''') from #B ',
@s4=isnull(@s4+'+','')+'@'+rtrim(Colid)
from
syscolumns
where
id=object_id('#Tempdb..#B') and Name not in(N'大类')
--print @s+' '+@s2+' '+@s3+' exec('+@s4+')' --显示执行语句
exec(@s+' '+@s2+' '+@s3+' exec('+@s4+')')
把變量改為nvarchar(max)再試試
风哥还是有错,我把你的代码放在存储过程里执行也错了,print没东西出来,你能不能放在存储过程里看看,不用存储过程是可以得出结果的
if not object_id(N'Tempdb..#D') is null
drop table #D select kind.type as 大类
,kind.name as 类别
,odetail.kind as 代码
,sum(case odetail.io when 'O' then odetail.qty else 0 end) as 购买件数
,sum(case odetail.io when 'I' then odetail.qty else 0 end) as 退货件数
,sum(case odetail.io when 'O' then odetail.qty else (-1) * odetail.qty end) as 实售件数
into #D
from jxcdb.dbo.odetail
inner join jxcdb.dbo.style on odetail.style = style.code
inner join jxcdb.dbo.cardscript on odetail.card = cardscript.cnumber
inner join jxcdb.dbo.kind on odetail.kind = kind.code
where odetail.kind in ('C','G','J','W','R','L','M','K','E','A','N','Y','P','Z','U','F','H','D','T')
and odetail.iodate >= '2011-5-1' --开始日期
group by kind.type,kind.name,odetail.kind
declare @s nvarchar(max),@s2 nvarchar(max),@s3 nvarchar(max),@s4 nvarchar(max)
select
@s=isnull(@s+',','declare ')+'@'+rtrim(Colid)+' nvarchar(max)',
@s2=isnull(@s2+',','select ')+'@'+rtrim(Colid)+'=N'''+
case when @s2 is not null then 'union all select' else ' select ' end+N' [大类]=N'''+quotename(Name,'''')+'''''',
@s3=isnull(@s3,'')+'select @'+rtrim(Colid)+'=@'+rtrim(Colid)+
N'+'',''+quotename([大类])+''=N''+quotename('+quotename(Name)+','''''''') from #D ',
@s4=isnull(@s4+'+','')+'@'+rtrim(Colid)
from
syscolumns
where
id=object_id('Tempdb..#D') and Name not in(N'大类')
print @s+' '+@s2+' '+@s3+' exec('+@s4+')' --显示执行语句
exec(@s+' '+@s2+' '+@s3+' exec('+@s4+')')
你的數據是否有字段為null
Tempdb..syscolumns --這里改改不好意思漏了這里,我測試的Tempdb--DB,所以就會報錯
没有为null的,那为什么我这样执行就没错呢
use tempdb
if not object_id(N'Tempdb..#D') is null
drop table #D select kind.type as 大类
,kind.name as 类别
,odetail.kind as 代码
,sum(case odetail.io when 'O' then odetail.qty else 0 end) as 购买件数
,sum(case odetail.io when 'I' then odetail.qty else 0 end) as 退货件数
,sum(case odetail.io when 'O' then odetail.qty else (-1) * odetail.qty end) as 实售件数
into #D
from jxcdb.dbo.odetail
inner join jxcdb.dbo.style on odetail.style = style.code
inner join jxcdb.dbo.cardscript on odetail.card = cardscript.cnumber
inner join jxcdb.dbo.kind on odetail.kind = kind.code
where odetail.kind in ('C','G','J','W','R','L','M','K','E','A','N','Y','P','Z','U','F','H','D','T')
and odetail.iodate >= '2011-5-1' --开始日期
group by kind.type,kind.name,odetail.kind
declare @s nvarchar(max),@s2 nvarchar(max),@s3 nvarchar(max),@s4 nvarchar(max)
select
@s=isnull(@s+',','declare ')+'@'+rtrim(Colid)+' nvarchar(max)',
@s2=isnull(@s2+',','select ')+'@'+rtrim(Colid)+'=N'''+
case when @s2 is not null then 'union all select' else ' select ' end+N' [大类]=N'''+quotename(Name,'''')+'''''',
@s3=isnull(@s3,'')+'select @'+rtrim(Colid)+'=@'+rtrim(Colid)+
N'+'',''+quotename([大类])+''=N''+quotename('+quotename(Name)+','''''''') from #D ',
@s4=isnull(@s4+'+','')+'@'+rtrim(Colid)
from
syscolumns
where
id=object_id('Tempdb..#D') and Name not in(N'大类')
print @s+' '+@s2+' '+@s3+' exec('+@s4+')' --显示执行语句
exec(@s+' '+@s2+' '+@s3+' exec('+@s4+')')
syscolumns --這里改改少了DB名--你的運行環境改變了,需要指定Tempdb..syscolumns