Question

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

数据表展示：
id name value
1 A 1
1 A 2
1 A 3
1 B 4
1 B 5
1 B 6
1 C 7
1 C 8
1 C 9
1 D 10
1 D 11
1 D 12

希望只用SQL查询得到结果为：

id nameA nameB nameC nameD
1 1 4 7 10
1 2 5 8 11
1 3 6 9 12
主要是把下面的查询结果由12行合并成3行数据，去掉空值。。。

Answer 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

效果如下

原来的格式

变为这种格式

Answer 2

具体什么数据库? 如果state_code少的话可以用语句直接执行,多的话需要考虑存储过程,但是不同数据库语法不同,请给出具体数据库。

追问

sql server 2012