行转列的三种实现方式
题目背景：数据库中有一张这样的表p_room(RoomGUID(PK),HUXING,RoomStru,OtherAttri)，其中主键RoomGUID表示房间标识，HUXING表示户型，如“CW”、“H3”、“T1”等；RoomStru表示房间结构，如“车位”、“两房两厅一卫”、“一房一厅一卫”；OtherAttri表示其他冗余字段。
题目要求：请按户型统计每种房间结构下的房间数目，显示格式如下表所示，写出查询语句。

HUXING
车位
两房两厅一卫
一房一厅一卫
CW
 
 
 
H3
 
 
 
H1
 
 
 

 
--solution1，利用case-when语句
SELECT HUXING,
       SUM(CASE RoomStru
           WHEN '车位' THEN 1
           ELSE 0 end) AS 车位,
       SUM(CASE RoomStru
           WHEN '两房两厅一卫' THEN 1
           ELSE 0 end) AS 两房两厅一卫,
       SUM(CASE RoomStru
           WHEN '一房一厅一卫' THEN 1
           ELSE 0 end) AS 一房一厅一卫
FROM dbo.p_Room
GROUP BY HUXING
 
--solution2,用标量子查询实现
Select     a.HUXING,
           (Select Count(*)
           From p_room
           Where Roomstru='车位' And
                 HUXING=a.HUXING) As 车位,
           (Select Count(*)
           From p_room
           Where Roomstru='两房两厅一卫' And
                 HUXING=a.HUXING) As 两房两厅一卫,
           (Select Count(*)
           From p_room
           Where Roomstru='一房一厅一卫' And
                 HUXING=a.HUXING) As 一房一厅一卫
From p_room a
GROUP BY a.HUXING
 
--solution3，用连接查询和多值子查询的结合实现，其中的Null值替换为值的操作由case-when实现
Select a.HUXING,
       (Case When 车位Is Null Then 0 Else 车位End) As 车位,
       (Case When 两房两厅一卫Is Null Then 0 Else 两房两厅一卫End) As 两房两厅一卫,
       (Case When 一房一厅一卫Is Null Then 0 Else 一房一厅一卫End) As 一房一厅一卫
From
(Select Distinct HUXING
 From p_room) a
Left Join
(Select HUXING,Count(*) As 车位
 From p_room
 Where RoomStru='车位'
 GROUP BY HUXING
) b
    On a.HUXING=b.HUXING
Left Join
(Select HUXING,Count(*) As 两房两厅一卫
 From p_room
 Where RoomStru='两房两厅一卫'
 GROUP BY HUXING
) c
    On a.HUXING=c.HUXING
Left Join
(Select HUXING,Count(*) As 一房一厅一卫
 From p_room
 Where RoomStru='一房一厅一卫'
 GROUP BY HUXING
) d
    On a.HUXING=d.HUXING
以上三种思路，仅供参考，欢迎提出建议。