DECLARE @p_Building TABLE

    (

      id INT ,

      BidName NVARCHAR(20)

    );

DECLARE @p_Room TABLE

    (

      id INT ,

      RoomNo INT ,

      RoomArea INT ,

      Bidid INT

    );

DECLARE @p_Customer TABLE

    (

      id INT ,

      CstName NVARCHAR(20) ,

      CstTel VARCHAR(20) ,

      CstSex NVARCHAR(5)

    );

DECLARE @p_Cst2Room TABLE

    (

      id INT ,

      RoomID INT ,

      CstID INT ,

      CstNO INT

    );

INSERT  INTO @p_Building( id, BidName ) VALUES  ( 1, N'四栋' );

INSERT  INTO @p_Building( id, BidName ) VALUES  ( 2, N'一栋' );

INSERT  INTO @p_Building( id, BidName ) VALUES  ( 3, N'二栋' );

INSERT  INTO @p_Building( id, BidName ) VALUES  ( 4, N'三栋' );

INSERT INTO @p_Room( id, RoomNo, RoomArea, Bidid ) VALUES  ( 1, 101, 80, 1)

INSERT INTO @p_Room( id, RoomNo, RoomArea, Bidid ) VALUES  ( 2, 102, 80, 2)

INSERT INTO @p_Room( id, RoomNo, RoomArea, Bidid ) VALUES  ( 3, 103, 99, 3)

INSERT INTO @p_Room( id, RoomNo, RoomArea, Bidid ) VALUES  ( 4, 104, 87, 4)

INSERT INTO @p_Customer( id, CstName, CstTel, CstSex )VALUES  ( 1,N'张三','',N'')

INSERT INTO @p_Customer( id, CstName, CstTel, CstSex )VALUES  ( 2,N'李四','',N'')

INSERT INTO @p_Customer( id, CstName, CstTel, CstSex )VALUES  ( 3,N'王五','',N'')

INSERT INTO @p_Customer( id, CstName, CstTel, CstSex )VALUES  ( 4,N'赵六','',N'')

INSERT INTO @p_Cst2Room( id, RoomID, CstID, CstNO )VALUES  ( 1,1,1,1)

INSERT INTO @p_Cst2Room( id, RoomID, CstID, CstNO )VALUES  ( 2,1,2,2)

INSERT INTO @p_Cst2Room( id, RoomID, CstID, CstNO )VALUES  ( 3,1,3,3)

INSERT INTO @p_Cst2Room( id, RoomID, CstID, CstNO )VALUES  ( 4,1,4,4)

SELECT  A.BidName AS [楼栋名称] ,

        B.RoomNo AS [房号] ,

        ( SELECT    C.CstName + ','

          FROM      @p_Customer AS C

                    INNER JOIN @p_Cst2Room AS D ON C.id = D.CstID

                                                   AND D.RoomID = B.id

        FOR

          XML PATH('')

        ) AS [客户名称] ,

        B.RoomArea AS [房间面积]

FROM    @p_Building AS A

        INNER JOIN @p_Room AS B ON A.id = B.Bidid; --AND A.id=1

SELECT  A.BidName AS [楼栋名称] ,

        CHARINDEX(SUBSTRING(A.BidName, 1, 1), '一二三四五六七八九十') AS OrderNum ,

        B.RoomNo AS [房号] ,

        B.RoomArea AS [房间面积]

FROM    @p_Building AS A

        INNER JOIN @p_Room AS B ON A.id = B.Bidid

ORDER BY CHARINDEX(SUBSTRING(A.BidName, 1, 1), '一二三四五六七八九十');