sql怎么将数据表中的一列按照值拆分为多列

2025-05-13 07:30:16

推荐回答（1个）

回答（1）：

有点多，你可能不想看，自己整理一下，细一点弄成视图也好

SELECT PEAppM0b02,COUNT([COUNT])[COUNT],CourseName,COUNT(A)A,COUNT(B)B,COUNT(C)C,COUNT(D)D FROM (

SELECT PEAppM0b02,COUNT([COUNT])[COUNT],CourseName,

CASE PEAppM0b162

WHEN '一等奖' THEN PEAppM0b162 END A,

CASE PEAppM0b162

WHEN '二等奖' THEN PEAppM0b162 END B,

CASE PEAppM0b162

WHEN '三等奖' THEN PEAppM0b162 END C,

CASE ISNULL(PEAppM0b162,'')

WHEN '无奖项' THEN PEAppM0b162 END D

FROM

(

SELECT * FROM

(SELECT PEAppM0b02,COUNT(PEAppM0b162)[COUNT],CourseName,PEAppM0b162

FROM PEAppM002Paper where PEAppM0b162='一等奖' GROUP BY PEAppM0b02,CourseName,PEAppM0b162) A

UNION

(SELECT PEAppM0b02,COUNT(PEAppM0b162)[COUNT],CourseName,PEAppM0b162

FROM PEAppM002Paper where PEAppM0b162='二等奖' GROUP BY PEAppM0b02,CourseName,PEAppM0b162)

UNION

(SELECT PEAppM0b02,COUNT(PEAppM0b162)[COUNT],CourseName,PEAppM0b162

FROM PEAppM002Paper where PEAppM0b162='三等奖' GROUP BY PEAppM0b02,CourseName,PEAppM0b162)

UNION

(SELECT PEAppM0b02,COUNT(PEAppM0b162)[COUNT],CourseName,PEAppM0b162

FROM PEAppM002Paper where (PEAppM0b162='无奖项' OR ISNULL(PEAppM0b162,'')='无奖项') GROUP BY PEAppM0b02,CourseName,PEAppM0b162)

)TB

GROUP BY PEAppM0b02,CourseName,PEAppM0b162

)AS TAB

WHERE PEAppM0b02='164'

GROUP BY PEAppM0b02,CourseName

效果如下

原来的格式

变为这种格式