樓主修改一下,如做group by 時用left()函數,隻取需求的部分 : select left('物理A',2) --物理--函數,合並字符串欄位 --測試數據 /* CREATE table person_info(dept_name varchar(10),position_name varchar(10),person_name varchar(10)) INSERT INTO person_info SELECT '資訊部','軟件編碼員','小李' UNION ALL SELECT '資訊部','軟件編碼員','小王' UNION ALL SELECT '行政部','秘書','小芳' */ --合並函數 CREATE FUNCTION FunMergeCharField(@vchA varchar(10),@vchB varchar(10)) RETURNS varchar(8000) AS BEGIN DECLARE @r varchar(8000) SET @r='' SELECT @r=@r+','+person_name FROM person_info WHERE dept_name=@vchA and position_name=@vchB RETURN(substring(@r,2,8000)) END GO --删除测试 DROP TABLE person_info DROP FUNCTION FunMergeCharField--調用 SELECT dept_name,position_name,在職人員=dbo.FunMergeCharField(dept_name,position_name) FROM person_info GROUP BY dept_name,position_name go
看样子,A字段也就这两种形式:xxxE、xxxT,对吗?
select left('物理A',2) --物理--函數,合並字符串欄位
--測試數據
/*
CREATE table person_info(dept_name varchar(10),position_name varchar(10),person_name varchar(10))
INSERT INTO person_info
SELECT '資訊部','軟件編碼員','小李'
UNION ALL SELECT '資訊部','軟件編碼員','小王'
UNION ALL SELECT '行政部','秘書','小芳'
*/
--合並函數
CREATE FUNCTION FunMergeCharField(@vchA varchar(10),@vchB varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+person_name FROM person_info WHERE dept_name=@vchA and position_name=@vchB
RETURN(substring(@r,2,8000))
END
GO
--删除测试
DROP TABLE person_info
DROP FUNCTION FunMergeCharField--調用
SELECT dept_name,position_name,在職人員=dbo.FunMergeCharField(dept_name,position_name)
FROM person_info
GROUP BY dept_name,position_name
go