再向大家求一条MS语句

[zhlym]

向大家求一条MS语句

A表记录如下

barcode name
001 可乐
002 花生
003 水果
004 花朵
002 花生

B表记录如下
barcode ku num
001 仓库1 10
001 仓库5 50
002 仓库2 20
003 仓库1 30
004 仓库3 40
001 仓库6 5
003 仓库1 3

要的结果如下（说明；A和B表是依barcode字段为关联，一个货品有可能放在多个仓库里，如“001可乐”摆放在多个仓库里，仓库的列出只需在结果中一个字段里显示即可），麻烦大家下

001 可乐 仓库1 10 仓库5 50 仓库6 5
002 花生 仓库2 20
003 水果 仓库1 30 仓库1 3
004 花朵 仓库3 40
002 花生 仓库2 20

[dawugui]

http://community.csdn.net/Expert/topic/5428/5428303.xml?temp=.4644586
我这里类似,你先看看,我再帮你写.

[believe209]

我还是第一次见到这样的,学习!

顺便帮顶!

[zhlym]

dawugui(潇洒老乌龟) 看了一下你给出的例子，和我的还是有区别，重点就是就是结果中列出仓库时我是要在同一行同一个字段列出（你例子中并非如此）

[dawugui]

if object_id('pubs..A') is not null
drop table A
go
create table A(barcode varchar(10),name varchar(10))
insert into A(barcode,name) values('001', '可乐')
insert into A(barcode,name) values('002', '花生')
insert into A(barcode,name) values('003', '水果')
insert into A(barcode,name) values('004', '花朵')
go

if object_id('pubs..B') is not null
drop table B
go

create table B(barcode varchar(10),ku varchar(10),num int)
insert into B(barcode,ku,num) values('001', '仓库1', 10)
insert into B(barcode,ku,num) values('001', '仓库5', 50)
insert into B(barcode,ku,num) values('002', '仓库2', 20)
insert into B(barcode,ku,num) values('003', '仓库1', 30)
insert into B(barcode,ku,num) values('004', '仓库3', 40)
insert into B(barcode,ku,num) values('001', '仓库6', 5)
insert into B(barcode,ku,num) values('003', '仓库1', 3)
go

--select px=(select count(1) from B where barcode=t.barcode and num<t.num)+1 , * from B t

declare @sql varchar(8000)
set @sql = 'select barcode'
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then ku else null end) ku' + cast(px as varchar)
+ ' , sum(case px when ''' + cast(px as varchar) + ''' then num else null end) num' + cast(px as varchar)
from (select distinct px from (select px=(select count(1) from B where barcode=t.barcode and num<t.num)+1 , * from B t) m) as n
set @sql = @sql + ' from (select px=(select count(1) from B where barcode=t.barcode and num<t.num)+1 , * from B t) o group by barcode'
exec(@sql)

drop table A,B

/*
barcode ku1 num1 ku2 num2 ku3 num3
---------- ---------- ----------- ---------- ----------- ---------- -----------
001 仓库6 5 仓库1 10 仓库5 50
002 仓库2 20 NULL NULL NULL NULL
003 仓库1 3 仓库1 30 NULL NULL
004 仓库3 40 NULL NULL NULL NULL
*/

[dawugui]

if object_id('pubs..A') is not null
drop table A
go
create table A(barcode varchar(10),name varchar(10))
insert into A(barcode,name) values('001', '可乐')
insert into A(barcode,name) values('002', '花生')
insert into A(barcode,name) values('003', '水果')
insert into A(barcode,name) values('004', '花朵')
go

if object_id('pubs..B') is not null
drop table B
go

create table B(barcode varchar(10),ku varchar(10),num int)
insert into B(barcode,ku,num) values('001', '仓库1', 10)
insert into B(barcode,ku,num) values('001', '仓库5', 50)
insert into B(barcode,ku,num) values('002', '仓库2', 20)
insert into B(barcode,ku,num) values('003', '仓库1', 30)
insert into B(barcode,ku,num) values('004', '仓库3', 40)
insert into B(barcode,ku,num) values('001', '仓库6', 5)
insert into B(barcode,ku,num) values('003', '仓库1', 3)
go

--select px=(select count(1) from B where barcode=t.barcode and num<t.num)+1 , * from B t

declare @sql varchar(8000)
set @sql = 'select a.name , q.* from a, (select barcode'
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then ku else null end) ku' + cast(px as varchar)
+ ' , sum(case px when ''' + cast(px as varchar) + ''' then num else null end) num' + cast(px as varchar)
from (select distinct px from (select px=(select count(1) from B where barcode=t.barcode and num<t.num)+1 , * from B t) m) as n
set @sql = @sql + ' from (select px=(select count(1) from B where barcode=t.barcode and num<t.num)+1 , * from B t) o group by barcode) q where a.barcode = q.barcode'
exec(@sql)

drop table A,B
/*
name barcode ku1 num1 ku2 num2 ku3 num3
---------- ---------- ---------- ----------- ---------- ----------- ---------- ----
可乐 001 仓库6 5 仓库1 10 仓库5 50
花生 002 仓库2 20 NULL NULL NULL NULL
水果 003 仓库1 3 仓库1 30 NULL NULL
花朵 004 仓库3 40 NULL NULL NULL NULL
*/

[dawugui]

你如果只是合并成一列,看我下面给出的列.不加逗号即可.


--带符号合并行列转换

--有表t，其数据如下：
a b
1 1
1 2
1 3
2 1
2 2
3 1
--如何转换成如下结果:
a b
1 1,2,3
2 1,2
3 1

create table tb
(
a int,
b int
)
insert into tb(a,b) values(1,1)
insert into tb(a,b) values(1,2)
insert into tb(a,b) values(1,3)
insert into tb(a,b) values(2,1)
insert into tb(a,b) values(2,2)
insert into tb(a,b) values(3,1)
go

if object_id('pubs..f_hb') is not null
drop function f_hb
go

--创建一个合并的函数
create function f_hb(@a int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(b as varchar) from tb where a = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go

--调用自定义函数得到结果：
select distinct a ,dbo.f_hb(a) as b from tb

drop table tb

--结果
a b
----------- ------
1 1,2,3
2 1,2
3 1

（所影响的行数为 3 行）


多个前列的合并
数据的原始状态如下：
ID PR CON OP SC
001 p c 差 6
001 p c 好 2
001 p c 一般 4
002 w e 差 8
002 w e 好 7
002 w e 一般 1
===========================
用SQL语句实现，变成如下的数据
ID PR CON OPS
001 p c 差(6),好(2),一般(4)
002 w e 差(8),好(7),一般(1)

if object_id('pubs..tb') is not null
drop table tb
go

create table tb
(
id varchar(10),
pr varchar(10),
con varchar(10),
op varchar(10),
sc int
)

insert into tb(ID,PR,CON,OP,SC) values('001', 'p', 'c', '差', 6)
insert into tb(ID,PR,CON,OP,SC) values('001', 'p', 'c', '好', 2)
insert into tb(ID,PR,CON,OP,SC) values('001', 'p', 'c', '一般', 4)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w', 'e', '差', 8)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w', 'e', '好', 7)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w', 'e', '一般', 1)
go

if object_id('pubs..test') is not null
drop table test
go
select ID,PR,CON , OPS = op + '(' + cast(sc as varchar(10)) + ')' into test from tb

--创建一个合并的函数
if object_id('pubs..f_hb') is not null
drop function f_hb
go
create function f_hb(@id varchar(10),@pr varchar(10),@con varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(OPS as varchar) from test where id = @id and @pr = pr and @con = con
set @str = right(@str , len(@str) - 1)
return(@str)
End
go

--调用自定义函数得到结果：
select distinct id ,pr , con , dbo.f_hb(id,pr,con) as OPS from test

drop table tb
drop table test

--结果
id pr con OPS
---------- ---------- ---------- -------------------
001 p c 差(6),好(2),一般(4)
002 w e 差(8),好(7),一般(1)

（所影响的行数为 2 行）



create table b
(col varchar(20))

insert b values ('a')
insert b values ('b')
insert b values ('c')
insert b values ('d')
insert b values ('e')


declare @sql varchar(1024)
set @sql=''
select @sql=@sql+b.col+',' from (select col from b) as b
set @sql='select '''+@sql+''''
exec(@sql)

[zhlym]

dawugui(潇洒老乌龟)

看了你的语句，和我的结果有些出入，
1我A表的重复的货品不能只列一条
2就是可否把你目前生前的ku1 num1 ku2 num2 ku3 num3这些字段内容
变成ku1 -->num1 ku2-->num2 ku3-->num3 变成列在一字段上就行，因我仓库是动态的，不需把每个仓库字段单独分开

[ojuju10]

if object_id(N'a')<>0
drop table a

create table a (barcode varchar(10),name varchar(50))
insert into a select '001','可乐'
union all select '002','花生'
union all select '003','水果'
union all select '004','花朵'
union all select '002','花生'

if object_id(N'b')<>0
drop table b

create table b(barcode varchar(10),ku varchar(20),num int)
insert into b select '001','仓库1',10
union all select '001','仓库5',50
union all select '002','仓库2',20
union all select '003','仓库1',30
union all select '004','仓库3',40
union all select '001','仓库6',5
union all select '003','仓库1',3

if object_id(N'c')<>0
drop table c

select id=identity(int ,1,1) ,a.*,b.ku,b.num
into c from a left join b
on a.barcode=b.barcode

create function ab1
(
@barcode varchar(20),
@name varchar(20))
returns varchar(800)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+' '+ku +' '+rtrim(num) from c
where barcode=@barcode and name=@name
return @sql
end

select barcode,name, dbo.ab1(barcode,name) as ku from c
group by barcode,name

drop table a,b,c
drop function ab1

barcode name ku
---------- ------------------- ----------------------------
001 可乐 仓库1 10 仓库5 50 仓库6 5
002 花生 仓库2 20 仓库2 20
003 水果 仓库1 30 仓库1 3
004 花朵 仓库3 40

（所影响的行数为 4 行）

[中国风]

借用楼上例子:
create table A(barcode varchar(10),name varchar(10))
insert into A(barcode,name) values('001', '可乐')
insert into A(barcode,name) values('002', '花生')
insert into A(barcode,name) values('003', '水果')
insert into A(barcode,name) values('004', '花朵')


create table B(barcode varchar(10),ku varchar(10),num int)
insert into B(barcode,ku,num) values('001', '仓库1', 10)
insert into B(barcode,ku,num) values('001', '仓库5', 50)
insert into B(barcode,ku,num) values('002', '仓库2', 20)
insert into B(barcode,ku,num) values('003', '仓库1', 30)
insert into B(barcode,ku,num) values('004', '仓库3', 40)
insert into B(barcode,ku,num) values('001', '仓库6', 5)
insert into B(barcode,ku,num) values('003', '仓库1', 3)


select *,con=(select count(*) from b where barcode=ta.barcode and ku!>ta.ku)
from b as ta

declare @s varchar(4000)
set @s=''
select @s=@s+',[ku'+rtrim(con)+']=max(case con when '+rtrim(con)+' then ku else '''' end)'
+',[num'+rtrim(con)+']=max(case con when '+rtrim(con)+' then rtrim(num) else '''' end)'
from
(select *,con=(select count(*) from b where barcode=ta.barcode and ku!>ta.ku)
from b as ta)ta group by con
set @s='select a.name'+@s+' from a join
(select *,con=(select count(*) from b where barcode=ta.barcode and ku!>ta.ku)
from b as ta)ta on a.barcode=ta.barcode group by a.name,a.barcode order by a.barcode'
exec(@s)

[中国风]

name ku1 num1 ku2 num2 ku3 num3
---------- ---------- ------------ ---------- ------------ ---------- ------------
可乐 仓库1 10 仓库5 50 仓库6 5
花生 仓库2 20
水果 仓库1 30
花朵 仓库3 40

(4 行受影响)

[中国风]

create table A(barcode varchar(10),name varchar(10))
insert into A(barcode,name) values('001', '可乐')
insert into A(barcode,name) values('002', '花生')
insert into A(barcode,name) values('003', '水果')
insert into A(barcode,name) values('004', '花朵')


create table B(barcode varchar(10),ku varchar(10),num int)
insert into B(barcode,ku,num) values('001', '仓库1', 10)
insert into B(barcode,ku,num) values('001', '仓库5', 50)
insert into B(barcode,ku,num) values('002', '仓库2', 20)
insert into B(barcode,ku,num) values('003', '仓库1', 30)
insert into B(barcode,ku,num) values('004', '仓库3', 40)
insert into B(barcode,ku,num) values('001', '仓库6', 5)
insert into B(barcode,ku,num) values('003', '仓库1', 3)


select *,con=1
into #temp--生成临时表
from b order by barcode

--更新con
declare @barcode varchar(3),@i int
update #temp
set @i=case when barcode=@barcode then @i+1 else 1 end,@barcode=barcode,con=@i





执行:
declare @s varchar(4000)
set @s=''
select @s=@s+',[ku'+rtrim(con)+']=max(case con when '+rtrim(con)+' then ku else '''' end)'
+',[num'+rtrim(con)+']=max(case con when '+rtrim(con)+' then rtrim(num) else '''' end)'
from #temp group by con
set @s='select a.name'+@s+' from a join
#temp as ta on a.barcode=ta.barcode group by a.name,a.barcode order by a.barcode'
exec(@s)
name ku1 num1 ku2 num2 ku3 num3
---------- ---------- ------------ ---------- ------------ ---------- ------------
可乐 仓库1 10 仓库5 50 仓库6 5
花生 仓库2 20
水果 仓库1 30 仓库1 3
花朵 仓库3 40

(4 行受影响)

[zhlym]

谢谢各位的代码，虽然大家都写出来了，可是和我的结果还是不是有点出入，其中ojuju10(longdchuanren)的语句，请问，如果不用临时表可否可以？

[zhlym]

谢谢各位的代码，虽然大家都写出来了，可是和我的结果还是有点出入，其中ojuju10(longdchuanren)的语句，请问，如果不用临时表可否可以？再create function 这时为什么出错呢？如这个是函数，可否不用函数，直接用语句，谢谢

[dawugui]

--原来你要的是这个结果?
if object_id('pubs..A') is not null
drop table A
go
create table A(barcode varchar(10),name varchar(10))
insert into A(barcode,name) values('001', '可乐')
insert into A(barcode,name) values('002', '花生')
insert into A(barcode,name) values('003', '水果')
insert into A(barcode,name) values('004', '花朵')
go

if object_id('pubs..B') is not null
drop table B
go

create table B(barcode varchar(10),ku varchar(10),num int)
insert into B(barcode,ku,num) values('001', '仓库1', 10)
insert into B(barcode,ku,num) values('001', '仓库5', 50)
insert into B(barcode,ku,num) values('002', '仓库2', 20)
insert into B(barcode,ku,num) values('003', '仓库1', 30)
insert into B(barcode,ku,num) values('004', '仓库3', 40)
insert into B(barcode,ku,num) values('001', '仓库6', 5)
insert into B(barcode,ku,num) values('003', '仓库1', 3)
go

if object_id('pubs..f_hb') is not null
drop function f_hb
go

--创建一个合并的函数
create function f_hb(@barcode varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ' ' + cast(ku as varchar) + ' ' + cast(num as varchar) from B where barcode = @barcode
set @str = right(@str , len(@str) - 2)
return(@str)
End
go

--调用自定义函数得到结果：
select a.barcode , a.name , t.result from a,
(
select distinct barcode ,dbo.f_hb(barcode) as result from B
) t
where a.barcode = t.barcode

drop table A,b

/*
barcode name result
---------- ---------- -------------------------
001 可乐 库1 10 仓库5 50 仓库6 5
002 花生 库2 20
003 水果 库1 30 仓库1 3
004 花朵 库3 40

（所影响的行数为 4 行）
*/

[saiwong]

dawuguilihai

[zhlym]

谢dawugui(潇洒老乌龟)
你最后整合的语句，可否不用函数呢？因用函数我不太好定义字段，烦再指点

[dawugui]

在2000里面用函数.
2005可以不用.

[zhlym]

可否使改成不使用函数的形式呢？

[rehearts]

dawugui(潇洒老乌龟) 顶 向上看齐

[zhlym]

综合大家的语句，问题解决，谢谢各位