SQL-行转列（传参数存储过程）

[wisdom1]

我在查询界面
可选条件：
1：时间
2：供应商
3：部品

查询后得出：

--出行转列，看了其他贴也不行灵活应用，求大侠指点（完成马上结贴）

--备注：模具类型是不固定的，所以写成动态的，（传入参数1：时间 2：供应商 G3 3：部品 B3 ）
--谢谢
模具类型 A# B# C# D#
数量 40 20 5 15

先看举例的代码吧（调试了的）

create table #tb1 ---主表
(
id int,   ---主ID
部品 varchar(50),
供应商 nvarchar(50),
确认 varchar(50),
订单时间 datetime
)
insert into #tb1 select 2,'b2','G2','NG','2011-3-1'
insert into #tb1 select 3,'b3','G3','OK','2011-3-2'
insert into #tb1 select 4,'b2','G2','OK','2011-3-1'
insert into #tb1 select 5,'b3','G5','OK','2011-3-5'
insert into #tb1 select 6,'b2','G2','OK','2011-3-1'
insert into #tb1 select 7,'b2','G2','OK','2011-3-4'
go

-- Select * from #tb1  

create table #tb2
(
id int,       
tb1ID int,    ---#tb1 的主ID关联
数量 int,
模具类型 varchar(50)
) 
insert into #tb2 select 1,3,20,'A#'
insert into #tb2 select 2,3,20,'A#'
insert into #tb2 select 3,3,10,'B#'
insert into #tb2 select 4,3,10,'B#'
insert into #tb2 select 5,3,5,'C#'
insert into #tb2 select 6,2,5,'C#'
insert into #tb2 select 7,3,15,'D#'
insert into #tb2 select 8,2,15,'D#'
go
-- Select * from #tb2
--drop table #tb2

--条件：
--1：时间  2：供应商G3 3：部品B3 4.确认OK


--现在我搞出这样
select A.模具类型,sum(A.数量) as K_NGNO from #tb2 as A
inner join #tb1 as B
on A.tb1ID=B.id
where B.部品='b3' and 
B.订单时间 between '2011-03-1 00:00:00.000' and '2011-03-6 00:00:00.000' 
and B.确认='OK' and B.供应商='G3'
group by A.模具类型
go

--不是我要的效果（不行行转列）
A#	40
B#	20
C#	5
D#	15


--其实我想要如下效果，请大侠看下直接写出行转列呢？
--备注：模具类型是不固定的，所以写成动态的，（传入参数1：时间  2：供应商G3 3：部品B3 4.确认OK）
--谢谢

模具类型	A#	B#	C#	D#
数量	40	20	5	15

[wisdom1]

谢谢 是这个意思

ssp2009
请问下我运行时 会出错：
[code=SQL]
Msg 170, Level 15, State 1, Line 1
第 1 行: 'max' 附近有语法错误。
Msg 137, Level 15, State 1, Line 3
必须声明变量 '@str'。
Msg 137, Level 15, State 2, Line 3
必须声明变量 '@str'。
Msg 137, Level 15, State 2, Line 5
必须声明变量 '@str'。
Msg 137, Level 15, State 2, Line 8
必须声明变量 '@str'。

请问是什么问题呢 那结果是正确的 （如何运行出来的）

[--小F--]

declare @str varchar(100)
set @str=''
select
 @str=@str+','+'sum(case when 模具类型='''+模具类型+''' then 数量 else 0 end)['+模具类型+']' 
      from #tb2 group by 模具类型
set @str='select ''数量'' as [模具类型]'+@str+' from #tb2 A inner join #tb1 B on A.tb1ID=B.id where B.部品=''b3''
          and b.订单时间 between ''2011-03-1 00:00:00.000'' and ''2011-03-6 00:00:00.000'' and B.确认=''OK'' and B.供应商=''G3'''

exec (@str)

[梁清锋]

CREATE PROCEDURE dbo.[GetInfor] 
	@部品 varchar(50),
	@供应商 nvarchar(50),
	@订单时间1 datetime,
	@订单时间2 datetime
AS
declare @str varchar(max)
BEGIN
set @str=''
select @str=@str+','+'sum(case when 模具类型='''+模具类型+''' then 数量 else 0 end)['+模具类型+']' 
      from #tb2 group by 模具类型
set @str='select ''数量'' as [模具类型]'+@str+' from #tb2 A inner join #tb1 B on A.tb1ID=B.id where B.部品='+ @部品 +'
          and b.订单时间 between '+ @订单时间1 +' and '+ @订单时间2 +' and B.确认=''OK'' and B.供应商='+ @供应商 +''

exec (@str)
END
GO

EXEC GetInfor 部品，供应商，时间段1，时间段2

[wing7742]

90度的翻转...学习备案

[wisdom1]

谢谢各位
ssp2009
fredrickhu
lqf336

其实 ssp2009 写的 我也看出来可以的...

用fredrickhu ( declare @str varchar(100) --->更改varchar(1000) 运行OK）

lqf336 存储过程OK

待会结贴

[wisdom1]

原来Max无法识别 declare @str varchar(Max) 这个变量

修改为
declare @str varchar(8000) 再次感谢

[wisdom1]

不好意思
参数是3个 不是4个

传入参数

1：时间 2：供应商（G3） 3：部品（B3）

[--小F--]

*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。

问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)： 
姓名 语文 数学 物理 
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
-------------------
*/

create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go

--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名

--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) 

--SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b

--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')

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

/*

[--小F--]

有点问题 好象是90度旋转 不是直接的行转列

[wisdom1]

谢谢~~大侠

这个我收藏看过了 可是不会灵活应用 SQL不是很熟悉

请指点下~~（我上面例子可以调试的，帮忙写下...以后慢慢理解）

[--小F--]

你先参考下这个,我吃饭了再看看
/*
将表数据旋转90度(2007-11-19于海南三亚)

将下表数据：
A                    b           c           d           e           
-------------------- ----------- ----------- ----------- ----------- 
x                    1           2           3           4
y                    5           6           7           8
z                    9           10          11          12

转化成如下结果：
a                    x          y          z          
-------------------- ---------- ---------- ---------- 
b                    1          5          9
c                    2          6          10
d                    3          7          11
e                    4          8          12

*/

--生成测试数据
create table test1(A varchar(20),b int,c int,d int,e int)
insert into test1 select 'x',1,2 ,3 ,4
insert into test1 select 'y',5,6 ,7 ,8
insert into test1 select 'z',9,10,11,12
go

--生成中间数据表
declare @s varchar(8000)
set @s = 'create table test2(a varchar(20)'
select @s = @s + ',' + A + ' varchar(10)' from test1
set @s = @s + ')'
exec(@s)
print @s
--借助中间表实现行列转换
declare @name varchar(20)

declare t_cursor cursor for 
select name from syscolumns 
where id=object_id('test1') and colid > 1 order by colid

open t_cursor

fetch next from t_cursor into @name

while @@fetch_status = 0
begin
    exec('select ' + @name + ' as t into test3 from test1')
    set @s='insert into test2 select ''' + @name + ''''
    select @s = @s + ',''' + rtrim(t) + '''' from test3
    exec(@s)
    exec('drop table test3')
    fetch next from t_cursor into @name
end
close t_cursor
deallocate t_cursor

--查看行列互换处理结果
select * from test1
select * from test2

--删除表
drop table test1
drop table test2
----------------------------------------------------------------------------

[wisdom1]

谢谢大侠 是90度 旋转~~~ 我看下先

补充：

#tb2 中的数量就是 不良数量（返回的是所有有供应商 所有部品 不良数量）

和

主表#tb1关联（条件限制...求的就是 某供应商 某部品 在某段时间内 模具类型的 不良数量之和）

[快溜]

create table #tb2
(
id int,       
tb1ID int,    ---#tb1 的主ID关联
数量 int,
模具类型 varchar(50)
) 
insert into #tb2 select 1,3,20,'A#'
insert into #tb2 select 2,3,20,'A#'
insert into #tb2 select 3,3,10,'B#'
insert into #tb2 select 4,3,10,'B#'
insert into #tb2 select 5,3,5,'C#'
insert into #tb2 select 6,2,5,'C#'
insert into #tb2 select 7,3,15,'D#'
insert into #tb2 select 8,2,15,'D#'
go
-- Select * from #tb2
--drop table #tb2

--条件：
--1：时间  2：供应商G3 3：部品B3 4.确认OK

declare @str varchar(max)
set @str=''
select @str=@str+','+'sum(case when 模具类型='''+模具类型+''' then 数量 else 0 end)['+模具类型+']' 
      from #tb2 group by 模具类型
set @str='select ''数量'' as [模具类型]'+@str+' from #tb2'
exec (@str)
/*
模具类型 A#          B#          C#          D#
---- ----------- ----------- ----------- -----------
数量   40          20          10          30

[wisdom1]

TO ssp2009

有问题

是3个参数 1：时间 2：供应商（G3） 3：部品（B3）

效果：

模具类型 A# B# C# D#
数量 40 20 5 15

[快溜]

declare @str varchar(max)
set @str=''
select @str=@str+','+'sum(case when 模具类型='''+模具类型+''' then 数量 else 0 end)['+模具类型+']' 
      from #tb2 group by 模具类型
set @str='select ''数量'' as [模具类型]'+@str+' from #tb2 A inner join #tb1 B on A.tb1ID=B.id where B.部品=''b3''
          and b.订单时间 between ''2011-03-1 00:00:00.000'' and ''2011-03-6 00:00:00.000'' and B.确认=''OK'' and B.供应商=''G3'''

exec (@str)
/*
模具类型 A#          B#          C#          D#
---- ----------- ----------- ----------- -----------
数量   40          20          5           15

[梁清锋]

楼主意思是要一个存储过程，存储过程包含
可选条件：
1：时间
2：供应商
3：部品
三个参数，要救查询输出结果为

模具类型 A# B# C# D#
---- ----------- ----------- ----------- -----------
数量 40 20 5 15

格式。

对不九纹龙？！？