再向大家求一条MS语句

Question

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

Answer 1

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

Answer 2

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',       '花朵')
goif object_id('pubs..B') is not null
   drop table B
gocreate 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 tdeclare @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
*/

Answer 3

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',       '花朵')
goif object_id('pubs..B') is not null
   drop table B
gocreate 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 tdeclare @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
*/

Answer 4

你如果只是合并成一列,看我下面给出的列.不加逗号即可.
--带符号合并行列转换--有表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)
goif 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 tbdrop 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
gocreate 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)
goif 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 testdrop 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)

Answer 5

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

Answer 6

if object_id(N'a')<>0
drop table acreate 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 bcreate 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',3if object_id(N'c')<>0
drop table cselect id=identity(int ,1,1) ,a.*,b.ku,b.num 
into c  from a left join b
on a.barcode=b.barcodecreate 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,namedrop 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 行）

Answer 7

借用楼上例子:
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  tadeclare @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)

Answer 8

name       ku1        num1         ku2        num2         ku3        num3
---------- ---------- ------------ ---------- ------------ ---------- ------------
可乐         仓库1        10           仓库5        50           仓库6        5
花生         仓库2        20                                              
水果                                 仓库1        30                      
花朵         仓库3        40                                              (4 行受影响)

Answer 9

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 行受影响)

Answer 10

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

Answer 11

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

Answer 12

--原来你要的是这个结果?
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',       '花朵')
goif object_id('pubs..B') is not null
   drop table B
gocreate 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)
goif 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.barcodedrop 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 行）
*/

Answer 13

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

Answer 14

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