求一sql语句

[ijwsoft]

已知一个表格TableA，如下：
column1 column2
AA rrr
AA sss
AA ttt
BB ooo
BB ppp
请用SQL语句返回下面这样的结果
Column1 Column4
AA rrr/sss/ttt
BB ooo/ppp

[wcj1018_net]

用case when then end

[黄_瓜]

if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([column1] varchar(2),[column2] varchar(3))
insert [tb]
select 'AA','rrr' union all
select 'AA','sss' union all
select 'AA','ttt' union all
select 'BB','ooo' union all
select 'BB','ppp'
--------------开始查询--------------------------

select [column1], 
[column2]=stuff((select '/'+ltrim([column2])from tb t 
                 where [column1]=tb.[column1] for xml path('')), 1, 1, '') 
from [tb] 
group by [column1] 
/*
column1 column2
------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AA      rrr/sss/ttt
BB      ooo/ppp

*/

[黄_瓜]

SQL2005下利用XML进行项目的合并与拆分
　　通常情况下我们对相同项目进分组求和，那是相当的简单啦，只要select.. group by加聚合函数就行了，可是对于串一类的项目进行合并时就不这么简单了，同样分解一个按指定分隔符分隔的串或分析指定位置的串，在2000下我们通常是创建一个函数，然后分组或提取就行了，现我们主要讨论在2005下利用xml来完成这个工作。
　　1、先来一个简单点，如下的例子对aaa相同的项目合并。
------------------------------------------------------------------------
-- Author:  HappyFlyStone  
-- Date  :  2009-01-04 20:57:59
-- Ver:     Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) 
--          Apr 14 2006 01:12:25 
--          Copyright (c) 1988-2005 Microsoft Corporation
--          Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--      
------------------------------------------------------------------------
-- Test Data: tb
IF OBJECT_ID('tb') IS NOT NULL 
    DROP TABLE tb
Go
CREATE TABLE tb(aaa INT,bbb INT)
Go
INSERT INTO tb
SELECT 1,2 UNION ALL
SELECT 1,3 UNION ALL
SELECT 1,4 UNION ALL
SELECT 2,2 UNION ALL
SELECT 2,5 
GO
--查询1
select 
    aaa, 
    [values]=stuff(replace(replace((select [bbb] 
                                    from tb 
                                    where aaa=t.aaa for xml AUTO),
                                    '"/><tb bbb="',','),
                            '"/>',''),1,9,'')
from tb t
group by aaa 
--查询2
SELECT * 
FROM( 
    SELECT DISTINCT 
        aaa 
    FROM tb 
)A 
OUTER APPLY( 
    SELECT 
        [bbb]= STUFF(REPLACE(REPLACE( 
            ( 
                SELECT [bbb] FROM tb N 
                WHERE aaa = A.aaa 
                FOR XML AUTO 
            ), '<N bbb="', ','), '"/>', ''), 1, 1, '') 
)N 

--查询3
select 
    aaa, 
    [values]=stuff((select ','+ltrim([bbb])
                    from tb t 
                    where aaa=tb.aaa for xml path('')), 1, 1, '') 
from tb 
group by aaa 


drop table tb
--查询结果
/*
aaa         values
----------- ---------
1           2,3,4
2           2,5

(2 行受影响)

*/
2、来个两个表关联操作并实现行列转换的
------------------------------------------------------------------------
-- Author:  HappyFlyStone  
-- Date  :  2009-01-04 20:57:59
-- Ver:     Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) 
--          Apr 14 2006 01:12:25 
--          Copyright (c) 1988-2005 Microsoft Corporation
--          Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--      
------------------------------------------------------------------------

-- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL 
    DROP TABLE ta
Go
CREATE TABLE ta(pid INT,tid INT,name NVARCHAR(6))
Go
INSERT INTO ta
SELECT 1,1,'hy3500' UNION ALL
SELECT 1,2,'aabbcc' UNION ALL
SELECT 2,3,'1111' UNION ALL
SELECT 2,4,'2222' 
GO
-- Test Data: tb
IF OBJECT_ID('tb') IS NOT NULL 
    DROP TABLE tb
Go
CREATE TABLE tb(NAME NVARCHAR(2),id INT)
Go
INSERT INTO tb
 SELECT '型号',1 UNION ALL
 SELECT '参数',2 
GO
--Start
SELECT t.[name],A,B
FROM(
    SELECT 
        B.[NAME],
        CAST((SELECT [name] FROM TA WHERE PID = A.PID FOR XML PATH('')) AS XML) AS X
    FROM TA A
    LEFT JOIN TB B ON A.PID = B.ID
    GROUP BY B.[NAME],A.PID
) t
CROSS APPLY
    (SELECT A=t.x.value('/name[1]','VARCHAR(10)'),B = t.x.value('/name[2]','VARCHAR(10)')) M

--Result:
/*

c    a          b
---- ---------- ----------
参数   1111       2222
型号   hy3500     aabbcc

(2 行受影响)

*/
--End 



3、取特定分隔符分隔的串中指定位置的串
------------------------------------------------------------------------
-- Author:  HappyFlyStone  
-- Date  :  2009-01-04 20:57:59
-- Ver:     Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) 
--          Apr 14 2006 01:12:25 
--          Copyright (c) 1988-2005 Microsoft Corporation
--          Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--      
------------------------------------------------------------------------

DECLARE @t TABLE(c VARCHAR(20))
INSERT @t SELECT '双桥,9.6米,30.0吨'
UNION ALL SELECT 'aa,bb,cc,dd'
--通常情况如果项目在四个项目以内时，推荐一种方法：


SELECT  REPLACE(PARSENAME(XX,3),'$$','.') C ,
    REPLACE(PARSENAME(XX,2),'$$','.') B 
FROM
(
    SELECT REPLACE(REPLACE(c,'.','$$'),',','.') XX FROM @T
)AA
--result
/*
c                                   b         
--------------------------------- -----------
双桥                                 9.6米

（所影响的行数为 1 行）
*/
好，那我们来看看2005下XML如何处理的
SELECT A,B FROM
    (SELECT CAST('<r>' + REPLACE(c,',','</r><r>') + '</r>' AS XML) x,c FROM @t) a
CROSS APPLY
    (SELECT A=a.x.value('/r[1]','VARCHAR(10)'),B=a.x.value('/r[2]','VARCHAR(10)')) b

/*
A          B
---------- ----------
双桥         9.6米
aa         bb

(2 行受影响)
*/

4、把项目串的编码用相应名称代替
------------------------------------------------------------------------
-- Author:  HappyFlyStone  
-- Date  :  2009-01-04 20:57:59
-- Ver:     Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) 
--          Apr 14 2006 01:12:25 
--          Copyright (c) 1988-2005 Microsoft Corporation
--          Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--      
------------------------------------------------------------------------

-- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL 
    DROP TABLE ta
Go
CREATE TABLE ta(ID NVARCHAR(3),number varchar(20))
Go
INSERT INTO ta
 SELECT '001','1,2' UNION ALL
 SELECT '002','1,2,3' 
GO
-- Test Data: tb
IF OBJECT_ID('tb') IS NOT NULL 
    DROP TABLE tb
Go
CREATE TABLE tb(ID INT,name NVARCHAR(7))
Go
INSERT INTO tb
SELECT 1,'测试一' UNION ALL
SELECT 2,'测试二' UNION ALL
SELECT 3,'测试三' 
GO
--Start
--查询一
SELECT C.ID,NUMBER = STUFF(REPLACE(REPLACE((
    SELECT  B.NAME  AS NAME
    FROM TA A
    LEFT JOIN 
    ( SELECT ID,NAME
          FROM TB
    ) B 
    ON CHARINDEX(','+LTRIM(B.ID)+',',','+A.NUMBER+',')>0
    WHERE A.ID = C.ID
    FOR XML AUTO
    ),'"/><B NAME="',','),'"/>',''),1,9,'')
FROM TA C
--查询二
SELECT A.ID,NUMBER=STUFF(
    (SELECT ','+NAME 
     FROM TB 
     WHERE CHARINDEX(','+LTRIM(ID)+',',','+A.NUMBER+',')>0 
     FOR XML PATH(''),ROOT('R'),TYPE).value('/R[1]','NVARCHAR(MAX)')
    ,1,1,'') 
FROM TA A



--Result:
/*

id   number
---- -----------------
001  测试一,测试二
002  测试一,测试二,测试三

(2 行受影响)
*/
--End 


5、最后我们来说说折分吧
如：
/*
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           ccc
*/
一般我们在2000下会借助中间生成一个连续的序列，然后和表关联折分，在2005下我们可借助CTE生成一个序列然后再拆分　
CREATE TABLE TB(ID INT,VALUE VARCHAR(30))
INSERT INTO TB VALUES(1,'AA,BB')
INSERT INTO TB VALUES(2,'AAA,BBB,CCC')
GO
IF OBJECT_ID('TEMPDB..#NUM') IS NOT NULL
    DROP TABLE #NUM
GO
--2000
SELECT TOP 100 ID=IDENTITY(INT,1,1) INTO #NUM FROM SYS.SYSCOLUMNS A,SYS.SYSCOLUMNS B
SELECT 
    A.ID,[VALUE]=SUBSTRING(A.[VALUE],B.ID,CHARINDEX(',',A.[VALUE]+',',B.ID)-B.ID) 
FROM 
    TB A,#NUM B
WHERE
    CHARINDEX(',',','+A.[VALUE],B.ID)=B.ID --也可用 SUBSTRING(','+A.COL2,B.ID,1)=','
--2005
;WITH T
AS
(
    SELECT 1 AS ID 
    UNION ALL
    SELECT ID + 1 FROM T WHERE ID < 100)
SELECT 
    A.ID,[VALUE]=SUBSTRING(A.[VALUE],B.ID,CHARINDEX(',',A.[VALUE]+',',B.ID)-B.ID) 
FROM 
    TB A,T B
WHERE
    CHARINDEX(',',','+A.[VALUE],B.ID)=B.ID --也可用 SUBSTRING(','+A.COL2,B.ID,1)=','

DROP TABLE TB,#NUM


------------------------------------------------------------------------
-- Author:  HappyFlyStone  
-- Date  :  2009-01-04 20:57:59
-- Ver:     Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) 
--          Apr 14 2006 01:12:25 
--          Copyright (c) 1988-2005 Microsoft Corporation
--          Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--      
------------------------------------------------------------------------

CREATE TABLE TB(ID INT,VALUE VARCHAR(30))
INSERT INTO TB VALUES(1,'AA,BB')
INSERT INTO TB VALUES(2,'AAA,BBB,CCC')
GO
SELECT A.ID, B.VALUE
FROM(
    SELECT ID, 
    [value] = CONVERT(XML,'<ROOT><V>' + REPLACE([VALUE], ',', '</V><V>') + '</V></ROOT>') 
    FROM tb
)A
OUTER APPLY(
    SELECT value = N.v.value('.', 'varchar(100)') 
    FROM A.[value].nodes('/ROOT/V') N(v)
)B

DROP TABLE tb
--查询结果
/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc

(5 行受影响)
*/

[黄_瓜]

/****************************************************************************************************************************************************** 
合并分拆表数据 

整理人：中国风(Roy) 

日期:2008.06.06 
******************************************************************************************************************************************************/ 

--> --> (Roy)生成測試數據 

if not object_id('Tab') is null 
    drop table Tab 
Go 
Create table Tab([Col1] int,[Col2] nvarchar(1)) 
Insert Tab 
select 1,N'a' union all 
select 1,N'b' union all 
select 1,N'c' union all 
select 2,N'd' union all 
select 2,N'e' union all 
select 3,N'f' 
Go 

合并表: 

SQL2000用函数: 

go 
if object_id('F_Str') is not null 
    drop function F_Str 
go 
create function F_Str(@Col1 int) 
returns nvarchar(100) 
as 
begin 
    declare @S nvarchar(100) 
    select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1 
    return @S 
end 
go 
Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab 

go 

SQL2005用XML: 

方法1: 

select 
    a.Col1,Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'') 
from 
    (select distinct COl1 from Tab) a 
Cross apply 
    (select COl2=(select N','+Col2 from Tab where Col1=a.COl1 For XML PATH(''), ROOT('R'), TYPE))b 

方法2: 

select 
    a.Col1,COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44)) 
from 
    (select distinct COl1 from Tab) a 
cross apply 
    (select Col2=(select COl2 from Tab  where COl1=a.COl1 FOR XML AUTO, TYPE) 
                .query(' <Tab> 
                {for $i in /Tab[position() <last()]/@COl2 return concat(string($i),",")} 
                {concat("",string(/Tab[last()]/@COl2))} 
                </Tab>') 
                )b 

SQL05用CTE: 

;with roy as(select Col1,Col2,row=row_number()over(partition by COl1 order by COl1) from Tab) 
,Roy2 as 
(select COl1,cast(COl2 as nvarchar(100))COl2,row from Roy where row=1 
union all 
select a.Col1,cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row from Roy a join Roy2 b on a.COl1=b.COl1 and a.row=b.row+1) 
select Col1,Col2 from Roy2 a where row=(select max(row) from roy where Col1=a.COl1) order by Col1 option (MAXRECURSION 0) 


生成结果: 
/* 
Col1        COl2 
----------- ------------ 
1          a,b,c 
2          d,e 
3          f 

(3 行受影响) 
*/ 


--参考
拆分表:

--> --> (Roy)生成測試數據
 
if not object_id('Tab') is null
    drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'f'
Go

--SQL2000用辅助表:
if object_id('Tempdb..#Num') is not null
    drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select 
    a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) 
from 
    Tab a,#Num b
where
    charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','
--2000不使用辅助表
Select
    a.Col1,COl2=substring(a.Col2,b.number,charindex(',',a.Col2+',',b.number)-b.number) 
from 
    Tab a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col2)
where
     substring(','+a.COl2,b.number,1)=','


SQL2005用Xml:

select 
    a.COl1,b.Col2
from 
    (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a
outer apply
    (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b




SQL05用CTE:

;with roy as 
(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab
union all
select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
)
select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)

生成结果:
/*
Col1        COl2
----------- -----
1           a
1           b
1           c
2           d
2           e
3           f
*/

[开着拖拉机泡妞]

--> 测试数据：[tbl]
go
if object_id('[tbl]') is not null 
drop table [tbl]
go
create table [tbl](
[column1] varchar(2),
[column2] varchar(3)
)
go
insert [tbl]
select 'AA','rrr' union all
select 'AA','sss' union all
select 'AA','ttt' union all
select 'BB','ooo' union all
select 'BB','ppp'

GO
IF OBJECT_ID('dbo.f_str') IS NOT NULL
DROP FUNCTION dbo.f_str
GO
CREATE FUNCTION dbo.f_str(@column1 VARCHAR(10))
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r = @r + '/' + [column2]
    FROM TBL
    WHERE column1=@column1
    RETURN STUFF(@r, 1, 1, '')
END

SELECT column1 AS column3,
column4=dbo.f_str(column1) FROM TBL
GROUP BY column1

/*
column3	column4
AA	rrr/sss/ttt
BB	ooo/ppp
*/

[ijwsoft]

非常感谢大家

DECLARE @T1 table(UserID int , UserName nvarchar(50),CityName nvarchar(50));
insert into @T1 (UserID,UserName,CityName) values (1,'a','上海')
insert into @T1 (UserID,UserName,CityName) values (2,'b','北京')
insert into @T1 (UserID,UserName,CityName) values (3,'c','上海')
insert into @T1 (UserID,UserName,CityName) values (4,'d','北京')
insert into @T1 (UserID,UserName,CityName) values (5,'e','上海')

SELECT B.CityName,LEFT(B.UserList,LEN(B.UserList)-1) AS Value
FROM
(
SELECT CityName,
(SELECT UserName+'/' FROM @T1 WHERE CityName=A.CityName FOR XML PATH('')) AS UserList
FROM @T1 A
GROUP BY CityName
) B

/* 执行结果
CityName Value
----------- -----------------
北京 b/d
上海 a/c/e

(2 行受影响)
*/

[勿勿]

if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([column1] varchar(2),[column2] varchar(3))
insert [tb]
select 'AA','rrr' union all
select 'AA','sss' union all
select 'AA','ttt' union all
select 'BB','ooo' union all
select 'BB','ppp'

--column1 column2
--AA rrr
--AA sss
--AA ttt
--BB ooo
--BB ppp
--请用SQL语句返回下面这样的结果
--Column1 Column4
--AA rrr/sss/ttt
--BB ooo/ppp

select column1,
STUFF((select '/'+ column2 from tb for xml path('')),1,1,'')s
 from tb group by column1

column1 s
------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AA      rrr/sss/ttt/ooo/ppp
BB      rrr/sss/ttt/ooo/ppp

(2 行受影响)

[勿勿]

if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([column1] varchar(2),[column2] varchar(3))
insert [tb]
select 'AA','rrr' union all
select 'AA','sss' union all
select 'AA','ttt' union all
select 'BB','ooo' union all
select 'BB','ppp'

--column1 column2
--AA rrr
--AA sss
--AA ttt
--BB ooo
--BB ppp
--请用SQL语句返回下面这样的结果
--Column1 Column4
--AA rrr/sss/ttt
--BB ooo/ppp

select column1,
STUFF((select '/'+  column2  from tb   where t.column1=column1 for xml path('')),1,1,'')Column4
 from tb t group by column1


column1 Column4
------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AA      rrr/sss/ttt
BB      ooo/ppp

(2 行受影响)

[ijwsoft]

7楼结果不对的。谢谢！

[ijwsoft]

说错了，是8楼结果不对！

[ijwsoft]

8楼对，7楼不对。刚才看错了。不好意思，已经结贴了，分改不了。
谢谢大家！