2.查询行转列小技巧

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