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
二、使用自定義聚合函數(shù)
--1.先定義字符串聚合函數(shù)
create FUNCTION sumString
(
@Id varchar(1024)--聚合主鍵入?yún)?，可以多個(gè)
)
RETURNS varchar(1024)
AS
BEGIN
declare @Str varchar(1024)
set @Str = ''
select @Str = @Str +';'+ [Name] from Fee--修改表名 和分隔符
where [sID] = @Id? --多個(gè)主鍵修改條件
return @Str
END
GO
--2.使用上面聚合函數(shù)
select? dbo.sumString(sID),sID? from Fee
where sID='080726000000029'
group by? sID
