SQL 多字段，合并显示

SQL：
       --1. 创建表，添加测试数据
CREATE TABLE tb(id int, [value] varchar(10))
INSERT tb 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 tb
/**//*
id          value
----------- ----------
1           aa
1           bb
2           aaa
2           bbb
2           ccc

(5 row(s) affected)
*/


--2 在SQL2000只能用自定义函数实现
----2.1 创建合并函数fn_strSum，根据id合并value值
GO
CREATE FUNCTION dbo.fn_strSum(@id int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @values varchar(8000)
    SET @values = ''
    SELECT @values = @values + ',' + value FROM tb WHERE id=@id
    RETURN STUFF(@values, 1, 1, '')
END
GO

-- 调用函数
SELECT id, VALUE = dbo.fn_strSum(id) FROM tb GROUP BY id
DROP FUNCTION dbo.fn_strSum

----2.2 创建合并函数fn_strSum2，根据id合并value值
GO
CREATE FUNCTION dbo.fn_strSum2(@id int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @values varchar(8000)    
    SELECT @values = isnull(@values + ',', '') + value FROM tb WHERE id=@id
    RETURN @values
END
GO

-- 调用函数
SELECT id, VALUE = dbo.fn_strSum2(id) FROM tb GROUP BY id
DROP FUNCTION dbo.fn_strSum2


--3 在SQL2005中的新解法
----3.1 使用OUTER APPLY
SELECT * 
FROM (SELECT DISTINCT id FROM tb) A OUTER APPLY(
        SELECT [values]= STUFF(REPLACE(REPLACE(
            (
                SELECT value FROM tb N
                WHERE id = A.id
                FOR XML AUTO
            ), '<N value="', ','), '"/>', ''), 1, 1, '')
)N

----3.2 使用XML
SELECT id, [values]=STUFF((SELECT ','+[value] FROM tb t WHERE id=tb.id FOR XML PATH('')), 1, 1, '')
FROM tb
GROUP BY id

--4 删除测试表tb
drop table tb

/**//*
id          values
----------- --------------------
1           aa,bb
2           aaa,bbb,ccc

(2 row(s) affected)
*/

拆分表:

--> --> (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)=','


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
*/

'Access 模块建立如下自定义函数

VB code
Function Same(id As Integer) As String Dim rs As Recordset Dim st As String st = "" Set rs = CurrentDb.OpenRecordset("Select * From Table_A where id=" + Str(id)) Do Until rs.EOF st = st + rs.Fields("name").Value + "," rs.MoveNext Loop Same = st End Function


效果

 转:http://user.qzone.qq.com/15518371/2

转载于:https://www.cnblogs.com/OleRookie/p/5355886.html