sql问题 求大手帮忙额

[cc516855689]

省 市 县区 县区名称 人员

13 1301 130100 市局1 测试员1
13 1301 130100 市局1 测试员2
13 1301 130101 第一分局 测试员3
13 1302 130200 市局2 测试员4
13 1302 130200 市局2 测试员5

如何合并显示为

省 市 县区 县区名称 人员

13 1301 130100 市局 测试员1
测试员2
130101 第一分局 测试员3
1302 130200 市局2 测试员4
测试员5


就是一样的数据不再显示。。。
求大手帮忙额。
不知各位明白意思没有？

[勿勿]

好像明白了 把格式整理好了

[cc516855689]

省 市 县区 县区名称 人员

13 1301 130100 市局 测试员1
测试员2
130101 第一分局 测试员3
1302 130200 市局2 测试员4
测试员5
是这样额。。。

[AcHerat]

做四个排序列，依次case when判断。

[开着拖拉机泡妞]

表a 
name    num 
aa      1 
bb      2 
cc      4 
dd      4 
ee      2 
一条语句实现如下:
name 
aa,bb,cc,dd,ee 
DECLARE @STR VARCHAR(8000)
SELECT @STR=ISNULL(@STR+',','')+name FROM (SELECT DISTINCT NAME FROM A)AS T
SELECT @STR
问题描述：
无论是在sql 2000，还是在sql 2005 中,都没有提供字符串的聚合函数，
  所以，当我们在处理下列要求时，会比较麻烦：
有表tb, 如下：
id    value
1     aa
1     bb
2     aaa
2     bbb
2     ccc
需要得到结果：
id     values
1      aa,bb
2      aaa,bbb,ccc
即，group by id, 求value 的和（字符串相加）
1. 旧的解决方法
-- 1. 创建处理函数
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r = @r + ',' + value
    FROM tb
    WHERE id=@id
    RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数
SELECt id, values=dbo.f_str(id) 
FROM tb 
GROUP BY id
-- 2. 新的解决方法
-- 示例数据
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'
-- 查询处理
SELECT *FROM (SELECT DISTINCT Id FROM @t)A
OUTER APPLY(
    SELECT [values]= STUFF(REPLACE(REPLACE(
            (   SELECT value FROM @t N
                WHERE id = A.id
                FOR XML AUTO
             ), '<N value="', ','), '"/>', ''), 1, 1, '')
)N
/*--结果
id          values
----------- ----------------
1           aa,bb
2           aaa,bbb,ccc
(2 行受影响)
--*/
--各种字符串分函数
--3.3.1 使用游标法进行字符串合并处理的示例。
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
--合并处理
--定义结果集表变量
DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))
--定义游标并进行合并处理
DECLARE tb CURSOR LOCAL
FOR
SELECT col1,col2 FROM tb ORDER BY  col1,col2
DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)
OPEN tb
FETCH tb INTO @col1,@col2
SELECT @col1_old=@col1,@s=''
WHILE @@FETCH_STATUS=0
BEGIN
    IF @col1=@col1_old
        SELECT @s=@s+','+CAST(@col2 as varchar)
    ELSE
    BEGIN
        INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
        SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1
    END
    FETCH tb INTO @col1,@col2
END
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
CLOSE tb
DEALLOCATE tb
--显示结果并删除测试数据
SELECT * FROM @t
DROP TABLE tb
/*--结果
col1       col2
a          1,2
b          1,2,3
--*/
GO
--3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
GO
--合并处理函数
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
    DECLARE @re varchar(100)
    SET @re=''
    SELECT @re=@re+','+CAST(col2 as varchar)
    FROM tb
    WHERE col1=@col1
    RETURN(STUFF(@re,1,1,''))
END
GO
--调用函数
SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1
--删除测试
DROP TABLE tb
DROP FUNCTION f_str
/*--结果
col1       col2
a          1,2
b          1,2,3
--*/
GO
--3.3.3 使用临时表实现字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
--合并处理
SELECT col1,col2=CAST(col2 as varchar(100)) 
INTO #t FROM tb
ORDER BY col1,col2
DECLARE @col1 varchar(10),@col2 varchar(100)
UPDATE #t SET 
    @col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END,
    @col1=col1,
    col2=@col2
SELECT * FROM #t
/*--更新处理后的临时表
col1       col2
a          1
a          1,2
b          1
b          1,2
b          1,2,3
--*/
--得到最终结果
SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1
/*--结果
col1       col2
a          1,2
b          1,2,3
--*/
--删除测试
DROP TABLE tb,#t
GO
--3.3.4.1 每组<=2 条记录的合并
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'c',3
--合并处理
SELECT col1,
    col2=CAST(MIN(col2) as varchar)
        +CASE 
            WHEN COUNT(*)=1 THEN ''
            ELSE ','+CAST(MAX(col2) as varchar)
        END
FROM tb
GROUP BY col1
DROP TABLE tb
/*--结果
col1       col2      
---------- ----------
a          1,2
b          1,2
c          3
--*/
--3.3.4.2 每组<=3 条记录的合并
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
UNION ALL SELECT 'c',3
--合并处理
SELECT col1,
    col2=CAST(MIN(col2) as varchar)
        +CASE 
            WHEN COUNT(*)=3 THEN ','
                +CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN(MAX(a.col2),MIN(a.col2))) as varchar)
            ELSE ''
        END
        +CASE 
            WHEN COUNT(*)>=2 THEN ','+CAST(MAX(col2) as varchar)
            ELSE ''
        END
FROM tb a
GROUP BY col1
DROP TABLE tb
/*--结果
col1       col2
---------- ------------
a          1,2
b          1,2,3
c          3
--*/
GO
if not object_id('A') is null
    drop table A
Go
Create table A([id] int,[cname] nvarchar(2))
Insert A
select 1,N'张三' union all
select 2,N'李四' union all
select 3,N'王五' union all
select 4,N'蔡六'
Go
--> --> 
 
if not object_id('B') is null
    drop table B
Go
Create table B([id] int,[cname] nvarchar(5))
Insert B
select 1,N'1,2,3' union all
select 2,N'3,4'
Go
create function F_str(@cname nvarchar(100))
returns nvarchar(100)
as
begin 
select @cname=replace(@cname,ID,[cname]) from A where patindex('%,'+rtrim(ID)+',%',','+@cname+',')>0
return @cname
end
go
select [id],dbo.F_str([cname])[cname] from B

id          cname
1           张三,李四,王五
2           王五,蔡六

(2 個資料列受到影響)
我现在有一字段值为:a,b,c 
分隔符为逗号. 
我现在想实现目地值为: 
字段: 
id  name 
1  a 
2  b 
3  c 
在SQL中咋实现呢?
declare @str varchar(8000) 
set @str = 'a1,b1,c2,d1,e3,f5' 
set @str =  'select  name='''+replace(@str,',',''''+' union all select ''')+'''' 
set @str='select id=identity(int,1,1),name into #temp from ('+@str+') a select * from #temp drop table #temp'
exec(@str)
--参考:
CREATE TABLE TB(ID VARCHAR(6), COLOR NVARCHAR(30))
INSERT TB
SELECT '173160',  N'#特深蓝色,#特深蓝色' UNION ALL 
SELECT '173160',  N'#特深蓝色,#特深蓝色' UNION ALL 
SELECT '911169',  N'#宝蓝色,#宝蓝色,#花灰色,#花灰色' UNION ALL 
SELECT '911169',  N'#宝蓝色,#宝蓝色,#花灰色,#花灰色' UNION ALL 
SELECT '911169',  N'#宝蓝色,#宝蓝色,#花灰色,#花灰色' UNION ALL 
SELECT '911169',  N'#宝蓝色,#宝蓝色,#花灰色,#花灰色'
GO
CREATE FUNCTION F_getStr(@color nvarchar(30))
returns nvarchar(30)
as
begin
  declare @str nvarchar(30),@temp nvarchar(30)
  set @str=''
  set @temp=''
  while charindex(',', @color+',')>0
  begin
    set @temp=left(@color, charindex(',', @color+',')-1)
    if charindex(','+@temp+',', ','+@str+',')=0
      set @str=@str+','+@temp
    set @color=stuff(@color, 1, charindex(',', @color+','), '')    
  end
  return stuff(@str, 1, 1, '')
end
go
SELECT ID,dbo.F_getStr(COLOR) as COLOR FROM TB
DROP TABLE TB
DROP FUNCTION F_getStr
/*
ID     COLOR
173160 #特深蓝色
173160 #特深蓝色
911169 #宝蓝色,#花灰色
911169 #宝蓝色,#花灰色
911169 #宝蓝色,#花灰色
911169 #宝蓝色,#花灰色
*/

[cc516855689]

晕 不识别空格额 就是测试员2对到测试员1，测试员5 也是。
意思就是两行一样的不显示，只显示两行不一样的额。
能听懂吗？囧。。。

[AcHerat]

;with ach as
(
    select *,rida=row_number() over (partition by cola order by getdate()),
             ridb=row_number() over (partition by cola,colb order by getdate()),
             ridc=row_number() over (partition by cola,colb,colc order by getdate()),
             ridd=row_number() over (partition by cola,colb,colc,cold order by getdate())
    from tb
)

select (case when rida=1 then cola else '' end) cola,
       (case when ridb=1 then colb else '' end) colb,
       (case when ridc=1 then colc else '' end) colc,
       (case when ridd=1 then cold else '' end) cold,
       cole
from ach

[cc516855689]

id value
1 aa
1 bb
2 aaa
2 bbb
2 ccc

谢谢大手们额 我是想要这样的结果额。。。
id value
1 aa
1 bb
2 aaa
2 bbb
2 ccc

有颜色的这个去掉。。。

[cc516855689]

小三 2000的库额

[IT-Style]

use DBTest
go
if OBJECT_ID('tabTest') is not null drop table tabTest
go
create table tabTest
(
Province int,
City int,
Area int,
AreaName nvarchar(50),
UserName nvarchar(50)
)
insert into tabTest
select 13,1301,130100,'市局1','测试员1' union all
select 13,1301,130100,'市局1','测试员2' union all
select 13,1301,130101,'第一分局','测试员2' union all
select 13,1302,130200,'市局2','测试员4' union all
select 13,1302,130200,'市局2','测试员4'
go

create function GetNames(@Province int,@City int,@Area int,@AreaName nvarchar(50))
returns nvarchar(800)
as
begin
declare   @s   nvarchar(800) 
set   @s= '' 
select   @s=@s + ','+UserName   from   tabTest  where   Province=@Province and City=@City and Area=@Area and AreaName=@AreaName
return    substring(@s,2,len(@s)-1)
end
go

SELECT Province,City,Area,AreaName,dbo.GetNames(Province,City,Area,AreaName) as Names
FROM tabTest
group by Province,City,Area,AreaName

[AcHerat]

select *,rid=identity(int,1,1) into #tb
from tb
order by cola,colb,colc,cold

select (case when (select count(*) from #tb where cola=t.cola and rid<=t.rid)=1 then cola else '' end) cola,
    (case when (select count(*) from #tb where cola=t.cola and colb=t.colb and rid<=t.rid)=1 then colb else '' end) colb,
    (case when (select count(*) from #tb where cola=t.cola and colb=t.colb and colc=t.colc and rid<=t.rid)=1 then colc else '' end) colc,
    ....
from #tb t

drop table #tb

[开着拖拉机泡妞]

--> 测试数据：[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([省] int,[市] int,[县区] int,[县区名称] varchar(8),[人员] varchar(7))
insert [tbl]
select 13,1301,130100,'市局1','测试员1' union all
select 13,1301,130100,'市局1','测试员2' union all
select 13,1301,130101,'第一分局','测试员3' union all
select 13,1302,130200,'市局2','测试员4' union all
select 13,1302,130200,'市局2','测试员5'


SELECT *FROM (SELECT DISTINCT [省],[市],[县区],[县区名称] FROM tbl)A
OUTER APPLY(
    SELECT [人员]= STUFF(REPLACE(REPLACE(
            (   SELECT [人员] FROM tbl N
                WHERE [省]= A.[省] AND [市]=A.市 AND [县区]=A.县区 AND [县区名称]=A.县区名称
                FOR XML AUTO
             ), '<N 人员="', ' '), '"/>', ''), 1, 1, '')
)N
/*
省	市	县区	县区名称	人员
13	1301	130100	市局1	测试员1 测试员2
13	1301	130101	第一分局	测试员3
13	1302	130200	市局2	测试员4 测试员5
*/

[AcHerat]

create table [tb](cola varchar(8),colb varchar(8),colc varchar(8),cold varchar(8),cole varchar(7))
insert [tb]
select '13','1301','130100','市局1','测试员1' union all
select '13','1301','130100','市局1','测试员2' union all
select '13','1301','130101','第一分局','测试员3' union all
select '13','1302','130200','市局2','测试员4' union all
select '13','1302','130200','市局2','测试员5'
go

select *,rid=identity(int,1,1) into #tb
from tb
order by cola,colb,colc,cold

select (case when (select count(*) from #tb where cola=t.cola and rid<=t.rid)=1 then cola else '' end) cola,
    (case when (select count(*) from #tb where cola=t.cola and colb=t.colb and rid<=t.rid)=1 then colb else '' end) colb,
    (case when (select count(*) from #tb where cola=t.cola and colb=t.colb and colc=t.colc and rid<=t.rid)=1 then colc else '' end) colc,
    (case when (select count(*) from #tb where cola=t.cola and colb=t.colb and colc=t.colc and cold=t.cold and rid<=t.rid)=1 then cold else '' end) cold,
    cole
from #tb t

drop table tb,#tb

/*****************************************

cola     colb     colc     cold     cole
-------- -------- -------- -------- -------
13       1301     130100   市局1     测试员1
                                     测试员2
                  130101   第一分局  测试员3
         1302     130200   市局2     测试员4
                                     测试员5

(5 行受影响)

[勿勿]

--省 市 县区 县区名称 人员
 
--13 1301 130100 市局1 测试员1 
--13 1301 130100 市局1 测试员2 
--13 1301 130101 第一分局 测试员3 
--13 1302 130200 市局2 测试员4
--13 1302 130200 市局2 测试员5
if OBJECT_ID('tb')is not null
drop table tb
go
create table tb (省  int,市  int,县区  int,县区名称 varchar(30),人员 varchar(30))
insert into tb values(13, 1301, 130100 ,'市局1','测试员1' )
insert into tb values(13 ,1301 ,130100 ,'市局1','测试员2' )
insert into tb values(13 ,1301 ,130101 ,'第一分局','测试员3'  )
insert into tb values(13 ,1302 ,130200 ,'市局2','测试员4' )
insert into tb values(13 ,1302 ,130200 ,'市局2','测试员5' )

 

--如何合并显示为

--省 市 县区 县区名称 人员
 
--13 1301 130100 市局     测试员1 
--                        测试员2 
--        130101 第一分局 测试员3
--  1302 130200  市局2    测试员4
--                        测试员5

  
;with ct as 
(
select *,rn=ROW_NUMBER()over(order by 人员) from tb 
  )

   select 省 ,市 ,县区 ,县区名称 ,人员 from ct  where  rn=1
    union all
select
 (select case 省 when t.省 then null else  省 end from ct where rn=t.rn+1  ) as 省 ,
 (select case 市 when t.市 then null else  市 end from ct where rn=t.rn+1  ) as 市 ,
 (select case 县区 when t.县区 then null else  县区 end from ct where rn=t.rn+1  ) as 县区 ,
  (select case 县区名称 when t.县区名称 then null else  县区名称 end from ct where rn=t.rn+1  ) as 县区名称 ,
  (select case 人员 when t.人员 then null else  人员 end from ct where rn=t.rn+1  ) as 人员  
from ct t where t.rn<5


 

省           市           县区          县区名称                           人员
----------- ----------- ----------- ------------------------------ ------------------------------
13          1301        130100      市局1                            测试员1
NULL        NULL        NULL        NULL                           测试员2
NULL        NULL        130101      第一分局                           测试员3
NULL        1302        130200      市局2                            测试员4
NULL        NULL        NULL        NULL                           测试员5

(5 行受影响)

[cc516855689]

ok 问题解决 谢谢各位老湿们。谢谢帮我解决问题的小三。。。果然名不虚传