求SQL语句

[zj8092]

表结构
表名：Table1
字段：id,车号，楼号
表数据如下：
id,车号, 楼号
1, 1, 1
2, 2, 1
3, 3, 1
4, 4, 2
5, 5, 2
我想得到如下结果
1号楼 2号楼
1 4
2 5
3
请问这个SQL语句怎么写

[geniuswjt]

基础行转列

[快溜]

又是行转列，哎。。

[zj8092]

能写个列子吗

[唐诗三百首]

create table table1(id int,车号 int, 楼号 int)

insert into table1
select 1, 1, 1 union all
select 2, 2, 1 union all
select 3, 3, 1 union all
select 4, 4, 2 union all
select 5, 5, 2

select 
isnull([1],0) '1号楼',
isnull([2],0) '2号楼'
from table1
pivot(max(车号) for 楼号 IN ([1], [2])) t

1号楼         2号楼
----------- -----------
1           0
2           0
3           0
0           4
0           5

[中国风]

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[车号] int,[楼号] int)
Insert #T
select 1,1,1 union all
select 2,2,1 union all
select 3,3,1 union all
select 4,4,2 union all
select 5,5,2
Go
select isnull(rtrim([1]),'') as [1楼号], isnull(rtrim([2]),'') as [2楼号]
FROM (Select row=row_number()over(PARTITION BY [楼号] ORDER BY [id]),[车号],[楼号] from #T)t PIVOT( MAX([车号]) FOR [楼号] IN([1],[2])) AS b
/*
1楼号	2楼号
1	4
2	5
3	
*/

[zj8092]

1号楼 2号楼
----------- -----------
1 4
2 5
3
我想要结果如上

[唐诗三百首]

这样更接近楼主的需求,

select 
a.[a1] '1号楼',
isnull(b.[b1],'') '2号楼'
from
(select row_number() over(order by (select 0)) rn,
cast([1] as varchar) 'a1'
from table1
pivot(max(车号) for 楼号 IN ([1], [2])) t
where [1] is not null) a
left join
(select row_number() over(order by (select 0)) rn,
cast([2] as varchar) 'b1'
from table1
pivot(max(车号) for 楼号 IN ([1], [2])) t
where [2] is not null) b
on a.rn=b.rn

1号楼                            2号楼
------------------------------ ------------------------------
1                              4
2                              5
3                              

(3 row(s) affected)

[zj8092]

谢谢，楼上二位仁兄，结贴

[中国风]

SQL2000時這樣用

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[车号] int,[楼号] int)
Insert #T
select 1,1,1 union all
select 2,2,1 union all
select 3,3,1 union all
select 4,4,2 union all
select 5,5,2
Go
declare @s nvarchar(2000)
select @s=isnull(@s+',',' select ')+N'[楼号'+RTRIM([楼号])+N']=max(case when [楼号]='+RTRIM([楼号])+N' then rtrim([车号]) else '''' end) '
from #T group by [楼号]

EXEC(@s+
' FROM (Select *,row=(SELECT COUNT(1) FROM #T WHERE [楼号]=t.[楼号] AND ID<=t.ID) from #T AS t)AS a GROUP BY row')

/*
楼号1	楼号2
1	4
2	5
3	
*/