表:
楼层 房号
一楼 101
一楼 102
一楼 105
二楼 201
二楼 202
二楼 205
请问如何使用 GROUP BY 语句得到如下结果:
楼层 房号1,房号2,房号3
一楼 101,102,105
二楼 201,202,205
注:每一个楼层不一定是3个房间
先把最多的记录的房间取出来,再行转列
Access时,参照
http://zhidao.baidu.com/question/158219080.html
以SQL Server为例,Oracle时用静态就行了
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#') is null
drop table #
Go
Create table #([楼层] nvarchar(2),[房号] int)
Insert #
select N'一楼',101 union all
select N'一楼',102 union all
select N'一楼',105 union all
select N'二楼',201 union all
select N'二楼',202 union all
select N'二楼',205
Go
DECLARE @s NVARCHAR(2000),@i NVARCHAR(2)
SET @s=''
Select TOP 1 @i=COUNT(*) from # GROUP BY 楼层 ORDER BY COUNT(*) desc
WHILE @i>0
SELECT @s=N',max(case when con='+@i+N' then [房号] end) as 房间'+@i+@s,@i=@i-1
EXEC('SELECT [楼层]'+@s+' FROM (SELECT con=(SELECT COUNT(1) FROM # WHERE 楼层=a.楼层 AND [房号]<=a.[房号]),* FROM # a)a group by [楼层]')
(6 个资料列受到影响)
楼层 房间1 房间2 房间3
---- ----------- ----------- -----------
一楼 101 102 105
二楼 201 202 205
(2 个资料列受到影响)
oracle:
SELECT [楼层],max(case when con=1 then [房号] end) as 房间1,max(case when con=2 then [房号] end) as 房间2,max(case when con=3 then [房号] end) as 房间3 FROM (SELECT con=(SELECT COUNT(1) FROM # WHERE 楼层=a.楼层 AND [房号]<=a.[房号]),* FROM # a)a group by [楼层]