求一个行转列语句

[大地主刘发财]

源表数据如下
ID 编号 姓名 小区 楼号 单元 楼层 门号
1 S001 张三 A小区 1 1 1 01
2 S002 李四 A小区 1 1 1 02
3 D001 王五 A小区 1 1 2 01
4 E001 赵六 A小区 1 1 2 02
5 S003 A张三 A小区 1 2 1 01
6 S004 A李四 A小区 1 2 1 02
7 D005 A王五 A小区 1 2 2 01
8 E006 A赵六 A小区 1 2 2 02

想查询出下面这样的状态
楼层单元 1 1-1 2 2-1
2 201王五 202赵六 201A王五 202A赵六
1 101张三 102李四 101A张三 102A李四
行为楼层，列为单元，每单元有多少户就出现几个列，这个户数是不固定的
例如1单元有2户，就出现列1和列1-1
请高手给个思路 现在完全两眼一抹黑

[-小蕾-]

http://www.cnblogs.com/maanshancss/archive/2013/03/13/2957108.html

[大地主刘发财]

引用 1 楼 zyl_leilei 的回复:

http://www.cnblogs.com/maanshancss/archive/2013/03/13/2957108.html

看过了 没用 完全不是一回事

[-小蕾-]

select 单元,楼层,门号=stuff((select ',' + 楼层 + 门号 + 姓名 from table4 as t where 单元=table4.单元 and 楼层=table4.楼层 for xml path('')),1,1,'')  
from table4  
group by 单元,楼层

[中国风]

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[编号] nvarchar(24),[姓名] nvarchar(23),[小区] nvarchar(23),[楼号] int,[单元] int,[楼层] int,[门号] nvarchar(22))
Insert #T
select 1,N'S001',N'张三',N'A小区',1,1,1,N'01' union all
select 2,N'S002',N'李四',N'A小区',1,1,1,N'02' union all
select 3,N'D001',N'王五',N'A小区',1,1,2,N'01' union all
select 4,N'E001',N'赵六',N'A小区',1,1,2,N'02' union all
select 5,N'S003',N'A张三',N'A小区',1,2,1,N'01' union all
select 6,N'S004',N'A李四',N'A小区',1,2,1,N'02' union all
select 7,N'D005',N'A王五',N'A小区',1,2,2,N'01' union all
select 8,N'E006',N'A赵六',N'A小区',1,2,2,N'02'
GO

SELECT  [楼号] ,
        [楼层] ,
        MAX(CASE WHEN [单元] = 1
                      AND [门号] = N'01' THEN RTRIM([楼层]) + [门号]+[姓名]
                 ELSE ''
            END) AS [1] ,
        MAX(CASE WHEN [单元] = 1
                      AND [门号] = N'02' THEN RTRIM([楼层]) + [门号]+[姓名]
                 ELSE ''
            END) AS [2-1] ,
        MAX(CASE WHEN [单元] = 2
                      AND [门号] = N'01' THEN RTRIM([楼层]) + [门号]+[姓名]
                 ELSE ''
            END) AS [2] ,
        MAX(CASE WHEN [单元] = 2
                      AND [门号] = N'02' THEN RTRIM([楼层]) + [门号]+[姓名]
                 ELSE ''
            END) AS [2-2]
FROM    #T
GROUP BY [楼号] ,
        [楼层]
ORDER BY [楼层] DESC
/*
楼号	楼层	1	2-1	2	2-2
1	2	201王五	202赵六	201A王五	202A赵六
1	1	101张三	102李四	101A张三	102A李四
*/

[大地主刘发财]

引用 4 楼 roy_88 的回复:

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[编号] nvarchar(24),[姓名] nvarchar(23),[小区] nvarchar(23),[楼号] int,[单元] int,[楼层] int,[门号] nvarchar(22))
Insert #T
select 1,N'S001',N'张三',N'A小区',1,1,1,N'01' union all
select 2,N'S002',N'李四',N'A小区',1,1,1,N'02' union all
select 3,N'D001',N'王五',N'A小区',1,1,2,N'01' union all
select 4,N'E001',N'赵六',N'A小区',1,1,2,N'02' union all
select 5,N'S003',N'A张三',N'A小区',1,2,1,N'01' union all
select 6,N'S004',N'A李四',N'A小区',1,2,1,N'02' union all
select 7,N'D005',N'A王五',N'A小区',1,2,2,N'01' union all
select 8,N'E006',N'A赵六',N'A小区',1,2,2,N'02'
GO

SELECT  [楼号] ,
        [楼层] ,
        MAX(CASE WHEN [单元] = 1
                      AND [门号] = N'01' THEN RTRIM([楼层]) + [门号]+[姓名]
                 ELSE ''
            END) AS [1] ,
        MAX(CASE WHEN [单元] = 1
                      AND [门号] = N'02' THEN RTRIM([楼层]) + [门号]+[姓名]
                 ELSE ''
            END) AS [2-1] ,
        MAX(CASE WHEN [单元] = 2
                      AND [门号] = N'01' THEN RTRIM([楼层]) + [门号]+[姓名]
                 ELSE ''
            END) AS [2] ,
        MAX(CASE WHEN [单元] = 2
                      AND [门号] = N'02' THEN RTRIM([楼层]) + [门号]+[姓名]
                 ELSE ''
            END) AS [2-2]
FROM    #T
GROUP BY [楼号] ,
        [楼层]
ORDER BY [楼层] DESC
/*
楼号	楼层	1	2-1	2	2-2
1	2	201王五	202赵六	201A王五	202A赵六
1	1	101张三	102李四	101A张三	102A李四
*/

感谢! 已经很接近了 我自己再研究下