select min_lvl ,job_desc from jobs GROUP BY min_lvl,job_desc10 New Hire - Job not specified
25 Designer
25 Editor
25 Sales Representative
75 Acquisitions Manager
75 Operations Manager
75 Productions Manager
100 Public Relations Manager
120 Marketing Manager
140 Managing Editor
150 Publisher
175 Business Operations Manager
175 Chief Financial Officier
200 Chief Executive Officer希望得到的结果
10 New Hire - Job not specified
25 Designer , Editor , Sales Representative,
75 Acquisitions Manager , Operations Manager , Productions Manager,
100 Public Relations Manager
120 Marketing Manager
140 Managing Editor
150 Publisher
175 Business Operations Manager , Chief Financial Officier,,
200 Chief Executive Officer谢谢!!
25 Designer
25 Editor
25 Sales Representative
75 Acquisitions Manager
75 Operations Manager
75 Productions Manager
100 Public Relations Manager
120 Marketing Manager
140 Managing Editor
150 Publisher
175 Business Operations Manager
175 Chief Financial Officier
200 Chief Executive Officer希望得到的结果
10 New Hire - Job not specified
25 Designer , Editor , Sales Representative,
75 Acquisitions Manager , Operations Manager , Productions Manager,
100 Public Relations Manager
120 Marketing Manager
140 Managing Editor
150 Publisher
175 Business Operations Manager , Chief Financial Officier,,
200 Chief Executive Officer谢谢!!
select min_lvl,
job_desc=stuff((select ','+job_desc from jobs where min_lvl=t.min_lvl for xml path('')),1,1,'')
from jobs t
GROUP BY min_lvl
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-10 23:48:43
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([minlvl] INT,[job_desc] NVARCHAR(30))
INSERT [tb]
SELECT 10,N'New Hire - Job not specified' UNION ALL
SELECT 25,'Designer' UNION ALL
SELECT 25,'Editor' UNION ALL
SELECT 25,'Sales Representative' UNION ALL
SELECT 75,'Acquisitions Manager' UNION ALL
SELECT 75,'Operations Manager' UNION ALL
SELECT 75,'Productions Manager' UNION ALL
SELECT 100,'Public Relations Manager' UNION ALL
SELECT 120,'Marketing Manager' UNION ALL
SELECT 140,'Managing Editor' UNION ALL
SELECT 150,'Publisher' UNION ALL
SELECT 175,'Business Operations Manager' UNION ALL
SELECT 175,'Chief Financial Officier' UNION ALL
SELECT 200,'Chief Executive Officer'
GO
--SELECT * FROM [tb]-->SQL查询如下:
select distinct [minlvl],
[job_desc]=STUFF((select ','+[job_desc] from tb where [minlvl]=t.[minlvl] for xml path('')),1,1,'')
from tb t
/*
minlvl job_desc
----------- ---------------------------------------------------------------
10 New Hire - Job not specified
25 Designer,Editor,Sales Representative
75 Acquisitions Manager,Operations Manager,Productions Manager
100 Public Relations Manager
120 Marketing Manager
140 Managing Editor
150 Publisher
175 Business Operations Manager,Chief Financial Officier
200 Chief Executive Officer(9 行受影响)
*/
if object_id('[jobs]') is not null drop table [jobs]
go
create table [jobs]([min_lvl] int,[job_desc] varchar(50))
insert [jobs]
select 10,'New Hire - Job not specified' union all
select 25,'Designer' union all
select 25,'Editor' union all
select 25,'Sales Representative' union all
select 75,'Acquisitions Manager' union all
select 75,'Operations Manager' union all
select 75,'Productions Manager' union all
select 100,'Public Relations Manager' union all
select 120,'Marketing Manager' union all
select 140,'Managing Editor' union all
select 150,'Publisher' union all
select 175,'Business Operations Manager' union all
select 175,'Chief Financial Officier' union all
select 200,'Chief Executive Officer'
---查询---
select min_lvl,
job_desc=stuff((select ','+job_desc from jobs where min_lvl=t.min_lvl for xml path('')),1,1,'')
from jobs t
GROUP BY min_lvl---结果---
min_lvl job_desc
----------- ------------------------------------------------------------------------------------
10 New Hire - Job not specified
25 Designer,Editor,Sales Representative
75 Acquisitions Manager,Operations Manager,Productions Manager
100 Public Relations Manager
120 Marketing Manager
140 Managing Editor
150 Publisher
175 Business Operations Manager,Chief Financial Officier
200 Chief Executive Officer(9 行受影响)
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-10 23:48:43
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([minlvl] INT,[job_desc] NVARCHAR(30))
INSERT [tb]
SELECT 10,N'New Hire - Job not specified' UNION ALL
SELECT 25,'Designer' UNION ALL
SELECT 25,'Editor' UNION ALL
SELECT 25,'Sales Representative' UNION ALL
SELECT 75,'Acquisitions Manager' UNION ALL
SELECT 75,'Operations Manager' UNION ALL
SELECT 75,'Productions Manager' UNION ALL
SELECT 100,'Public Relations Manager' UNION ALL
SELECT 120,'Marketing Manager' UNION ALL
SELECT 140,'Managing Editor' UNION ALL
SELECT 150,'Publisher' UNION ALL
SELECT 175,'Business Operations Manager' UNION ALL
SELECT 175,'Chief Financial Officier' UNION ALL
SELECT 200,'Chief Executive Officer'
GO
--SELECT * FROM [tb]-->SQL查询如下:
create function fn_test(@minlvl int)
returns nvarchar(1000)
as
begin
declare @s varchar(1000)
select @s=isnull(@s+',','')+job_desc from tb where @minlvl=minlvl
return @s
end
go
select distinct minlvl,dbo.fn_test(minlvl) job_desc from tb
/*
minlvl job_desc
----------- ---------------------------------------------------------------
10 New Hire - Job not specified
25 Designer,Editor,Sales Representative
75 Acquisitions Manager,Operations Manager,Productions Manager
100 Public Relations Manager
120 Marketing Manager
140 Managing Editor
150 Publisher
175 Business Operations Manager,Chief Financial Officier
200 Chief Executive Officer(9 行受影响)
*/