mysql查询将1列的值分成多列_sql怎么将数据表中的一列按照值拆分为多列

展开全部

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

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 '一等奖32313133353236313431303231363533e4b893e5b19e31333431373264' 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

效果如下

原来的格式

变为这种格式