SQL Server 2008 - Display row items in column with

Question

I have three tables that store: TENANT, ROOMMATE and PERSON records.

The schema is as below:

Table: Tenant
HMYPERSON   SCODE   sFirstName sLastName
        1   t0011   fName       lName

Table: room
HMYPERSON   HMYTENANT   sRelationShip
    9458            1   Roommate
    1024            1   Other
    1025            1   Roommate
    1026            1   Guarantor

Table: Person
HMY     UCODE   ULASTNAME   sFIRSTNAME      iPersonType
9458    r0011   roommate1   roommate1Fname  93
1024    r0012   roommate2   roommate2Fname  93
1025    r0013   roommate3   roommate3Fname  93
1026    r0014   roommate4   roommate4Fname  90

TENANT table contains the TENANT records. ROOM table contains occupants record. And PERSON table contains all types of people in it (TENANT, ROOMMATE, VENDOR etc.)

HMYTENANT in ROOM table is a foreign key of TENANT table (HMYPERSON)

HMYPERSON in ROOM table is a foreign key of PERSON table (HMY)

IPERSONTYPE in PERSON table specifies what type of person record it is. (93 is roommate and 90 is a Guarantor)

I want to display all the tenant and their roommate names in a single row. All the roommates should be displayed in the same row and in separate columns. (One column for each roommate)

I have written a query that gives me the list of roommates, but in rows.

Here is what I have written:

Select t.scode, t.sLastname + ' ' + t.sFIRSTNAME TenantName, p.ULASTNAME + ' ' + p.sFIRSTNAME RoommateName
from tenant t
join room r on r.hmytenant = t.hmyperson
left join person p on p.hmy = r.hmyperson and (r.sRelationship <> 'Guarantor' or p.IPERSONTYPE<>90)
where t.hmyperson=1

This gives me output like below:

scode   Tenantname      RoommateName
t0011   lName fName     roommate1 roommate1Fname
t0011   lName fName     roommate2 roommate2Fname
t0011   lName fName     roommate3 roommate3Fname

But I want it like:

scode     Tenantname     Roommate1     Roommate2     Roommate3

Up to 5 roommates is sufficient.

Appreciate your help.

Answer 1

Can you try this?

WITH A AS (SELECT HMYPERSON, HMYTENANT, SRELATIONSHIP, P.ULASTNAME, p.SFIRSTNAME, ROW_NUMBER() OVER (PARTITION BY HMYTENANT ORDER BY HMYPERSON) AS RN
            FROM ROOM R
            INNER JOIN PERSON P ON P.HMY = R.HMYPERSON  
                                AND (R.SRELATIONSHIP <> 'Guarantor' OR P.IPERSONTYPE<>90)           
            )
SELECT T.SCODE, T.SLASTNAME + ' ' + T.SFIRSTNAME TENANTNAME
        , A1.ULASTNAME + ' ' + A1.SFIRSTNAME ROOMMATENAME_1
        , A2.ULASTNAME + ' ' + A2.SFIRSTNAME ROOMMATENAME_2
        , A3.ULASTNAME + ' ' + A3.SFIRSTNAME ROOMMATENAME_3
        , A4.ULASTNAME + ' ' + A4.SFIRSTNAME ROOMMATENAME_4
        , A5.ULASTNAME + ' ' + A5.SFIRSTNAME ROOMMATENAME_5
FROM TENANT T
LEFT JOIN A A1 ON A1.HMYTENANT = T.HMYPERSON AND A1.RN=1
LEFT JOIN A A2 ON A2.HMYTENANT = T.HMYPERSON AND A2.RN=2
LEFT JOIN A A3 ON A3.HMYTENANT = T.HMYPERSON AND A3.RN=3
LEFT JOIN A A4 ON A4.HMYTENANT = T.HMYPERSON AND A4.RN=4
LEFT JOIN A A5 ON A5.HMYTENANT = T.HMYPERSON AND A5.RN=5
WHERE T.HMYPERSON=1

Output:

SCODE TENANTNAME  ROOMMATENAME_1           ROOMMATENAME_2           ROOMMATENAME_3           ROOMMATENAME_4           ROOMMATENAME_5
----- ----------- ------------------------ ------------------------ ------------------------ ------------------------ ------------------------
t0011 lName fName roommate2 roommate2Fname roommate3 roommate3Fname roommate1 roommate1Fname NULL               NULL