1.直接判断
SELECT XX,
MAX(CASE iNo WHEN 1 THEN sCode ELSE '' END) AS sCode1,
MAX(CASE iNo WHEN 1 THEN sName ELSE '' END) AS sName1,
MAX(CASE iNo WHEN 2 THEN sCode ELSE '' END) AS sCode2,
MAX(CASE iNo WHEN 2 THEN sName ELSE '' END) AS sName2,
MAX(CASE iNo WHEN 3 THEN sCode ELSE '' END) AS sCode3,
MAX(CASE iNo WHEN 3 THEN sName ELSE '' END) AS sName3,
MAX(CASE iNo WHEN 4 THEN sCode ELSE '' END) AS sCode3,
MAX(CASE iNo WHEN 4 THEN sName ELSE '' END) AS sName3
FROM ICD
group by XX
二、使用自定义聚合函数
--1.先定义字符串聚合函数
create FUNCTION sumString
(
@Id varchar(1024)--聚合主键入参，可以多个
)
RETURNS varchar(1024)
AS
BEGIN
declare @Str varchar(1024)
set @Str = ''
select @Str = @Str +';'+ [Name] from Fee--修改表名 和分隔符
where [sID] = @Id --多个主键修改条件
return @Str
END
GO
--2.使用上面聚合函数
select dbo.sumString(sID),sID from Fee
where sID='080726000000029'
group by sID
