有表:
aa bb奥米茄 地板
瓷砖 地板
蒙娜丽莎 厨房设备
实木复合地板 涂料要得到这样的结果,即要按bb分组后,用"|"连接起来:CC DD
奥米茄 | 瓷砖 地板蒙娜丽莎 厨房设备实木复合地板 涂料......... ..........
aa bb奥米茄 地板
瓷砖 地板
蒙娜丽莎 厨房设备
实木复合地板 涂料要得到这样的结果,即要按bb分组后,用"|"连接起来:CC DD
奥米茄 | 瓷砖 地板蒙娜丽莎 厨房设备实木复合地板 涂料......... ..........
调试欢乐多
Create Table 表(id varchar(10),amount integer,re varchar(10))
--插入数据
insert into 表
select '1','3','aaa' union
select '1','5','bbb' union
select '1','4','ccc' union
select '2','10','pkoge' union
select '2','12','daf'
go
--测试语句
CREATE FUNCTION FunMergeCharField(@vchA varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+re FROM 表 WHERE id=@vchA
RETURN(substring(@r,2,8000))
END
GO
select id,sum(amount) as sum,dbo.FunMergeCharField(id) as re叠加 from 表 group by id
--删除测试环境
Drop Table 表
Drop FUNCTION FunMergeCharField
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(aa varchar(20),bb varchar(10))
go
insert tb SELECT
'奥米茄' , '地板' UNION ALL SELECT
'瓷砖' , '地板' UNION ALL SELECT
'蒙娜丽莎' , '厨房设备' UNION ALL SELECT
'实木复合地板' , '涂料'
go
CREATE FUNCTION dbo.f_tb(@id varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + '|' + aa FROM tb WHERE bb=@id
RETURN STUFF(@str, 1, 1, '')
END
GO
SELECT bb, CardName = dbo.f_tb(bb) FROM tb GROUP BY bb
drop table tb
drop function dbo.f_tb go
/*------------
bb CardName
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
厨房设备 蒙娜丽莎
地板 奥米茄|瓷砖
涂料 实木复合地板-------*/
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+'|'+aa FROM 表 WHERE bb=@vchA
RETURN(substring(@r,2,8000))
END
GOselect dbo.FunMergeCharField(bb),bb FROM 表 group by bb
----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-26 22:44:13
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([aa] varchar(12),[bb] varchar(8))
insert [tb]
select '奥米茄','地板' union all
select '瓷砖','地板' union all
select '蒙娜丽莎','厨房设备' union all
select '实木复合地板','涂料'
--------------开始查询--------------------------
SELECT *
FROM
(SELECT DISTINCT bb FROM tb)A
OUTER APPLY(
SELECT [aa]= STUFF(REPLACE(REPLACE(
(
SELECT aa FROM tb N
WHERE bb = A.bb
FOR XML AUTO
), '<N aa="', '|'), '"/>', ''), 1, 1, '')
)N
drop table tb
----------------结果----------------------------
/*bb aa
-------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
厨房设备 蒙娜丽莎
地板 奥米茄|瓷砖
涂料 实木复合地板(3 行受影响)
*/
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-26 22:44:13
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([aa] varchar(12),[bb] varchar(8))
insert [tb]
select '奥米茄','地板' union all
select '瓷砖','地板' union all
select '蒙娜丽莎','厨房设备' union all
select '实木复合地板','涂料'
--------------开始查询--------------------------
SELECT *
FROM
(SELECT DISTINCT bb FROM tb)A
OUTER APPLY(
SELECT [aa]= STUFF(REPLACE(REPLACE(
(
SELECT aa FROM tb N
WHERE bb = A.bb
FOR XML AUTO
), '<N aa="', '|'), '"/>', ''), 1, 1, '')
)N order by aa
drop table tb
----------------结果----------------------------
/*bb aa
-------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
地板 奥米茄|瓷砖
厨房设备 蒙娜丽莎
涂料 实木复合地板(3 行受影响)
*/
if object_id('tb') is not null
drop table tb
create table tb(aa varchar(20),bb varchar(20))
insert into tb select '奥米茄', '地板' union all
select '瓷砖' ,'地板' union all
select '蒙娜丽莎','厨房设备' union all
select '实木复合地板' ,'涂料' select [values]=stuff((select '|'+aa from tb t where bb=tb.bb for xml path('')), 1, 1, ''),bb
from tb
group by bb
/*bb values
-------------------- --------------------
厨房设备 蒙娜丽莎
地板 奥米茄|瓷砖
涂料 实木复合地板(3 行受影响)
*/
drop table tb
create table tb(aa varchar(20),bb varchar(20))
insert into tb select '奥米茄', '地板' union all
select '瓷砖' ,'地板' union all
select '蒙娜丽莎','厨房设备' union all
select '实木复合地板' ,'涂料'
--创建一个合并的函数
create function f_hb(@a varchar(20))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + '|' + aa from tb where bb = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
--调用自定义函数得到结果:
select distinct bb ,dbo.f_hb(bb) as value from tb
/*bb value
-------------------- --------------------
厨房设备 蒙娜丽莎
地板 奥米茄|瓷砖
涂料 实木复合地板(3 行受影响)
*/