日期:2014-05-20 浏览次数:21094 次
--房间状态,行列转换,根据floorid 4行并一行
SELECT floorid,FloorName,
max(CASE cindex WHEN 0 THEN name ELSE '' end) AS name1,
max(CASE cindex WHEN 0 THEN status ELSE '' end) AS status1,
max(CASE cindex WHEN 1 THEN name ELSE '' end) AS name2,
max(CASE cindex WHEN 1 THEN status ELSE '' end) AS status2,
max(CASE cindex WHEN 2 THEN name ELSE '' end) AS name3,
max(CASE cindex WHEN 2 THEN status ELSE '' end) AS status3,
max(CASE cindex WHEN 3 THEN name ELSE '' end) AS name4,
max(CASE cindex WHEN 3 THEN status ELSE '' end) AS status4
from
(SELECT roomid,
Name,
RoomTypeName,
FloorId,
FloorName,
rindex = (
(
ROW_NUMBER() OVER (partition BY floorid ORDER BY floorid)-1
)/4
),
cindex = (
(
ROW_NUMBER() OVER (partition BY floorid ORDER BY floorid)-1
)%4
),
AreaId,
AreaName,
Status,
SCode
FROM TblRoomList) AS t
GROUP BY FloorId,FloorName,rindex