--> 测试数据:[tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb]([SubCode] VARCHAR(3),[SbuName] VARCHAR(1)) INSERT [tb] SELECT '001','x' UNION ALL SELECT '002','x' --------------开始查询--------------------------SELECT DISTINCT SubCode =STUFF((SELECT ','+[SubCode] FROM [tb] WHERE [SbuName]=t.[SbuName] FOR XML PATH('')),1,1,''),[SbuName] FROM [tb] AS t ----------------结果---------------------------- /* SubCode SbuName ------------------------- 001,002 x*/
select distinct SubDepartment1Code,'all' as SubDepartment1Name from tblEmployee where SubDepartment1Code !='' and SubDepartment1Name is not null union all select distinct SubDepartment1Code,SubDepartment1Name from tblEmployee where SubDepartment1Code !='' and SubDepartment1Name is not null 输出结果是 SubCode SbuName 001 x 002 x 001 开发 002 灌装只要x合并成一行 其他不合并
001 x
002 x
由于SbuName要绑定在下拉框上,所以要求把SbuName x查询成一行.
显示
SubCode SbuName
001,002 x
不写了
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([SubCode] VARCHAR(3),[SbuName] VARCHAR(1))
INSERT [tb]
SELECT '001','x' UNION ALL
SELECT '002','x'
--------------开始查询--------------------------SELECT DISTINCT SubCode =STUFF((SELECT ','+[SubCode] FROM [tb] WHERE [SbuName]=t.[SbuName] FOR XML PATH('')),1,1,''),[SbuName]
FROM [tb] AS t
----------------结果----------------------------
/*
SubCode SbuName
-------------------------
001,002 x*/
union all
select distinct SubDepartment1Code,SubDepartment1Name from tblEmployee where SubDepartment1Code !='' and SubDepartment1Name is not null 输出结果是
SubCode SbuName
001 x
002 x
001 开发
002 灌装只要x合并成一行 其他不合并