SQL Server Query

Question

I have following tables:

Table Name : LandParces

Blockid,  PNo, Name,             Address
=========================================
32000101  5    Chandana De Silva Colombo
32000101  8    Fernance          Badulla
32000203  4    Sunil Fernando    Tangalle

Table Name = Owner

Blockid,  PNo,  StorP   Hec
===============================
32000101  5     St      0.5020   
32000101  8     Pr      2.1045
32000203  4     Pr      0.1824

Table Name = Encumbrances

Blockid,      PNo,  Encumbrance
===============================
32000101     5      Life
32000101     5      Mortgage
32000101     5      access
32000101     8      Life
32000101     8      access
32000203     4      access road

Now I want to get the result as follows

Blockid, PNo, Name,          Address, Encumbrance           StorP   Hec           
==========================================================================
32000101 5    Chandana...    Colombo  Life,Mortgage,access  St      0.5020
32000101 8    Fernance       Badulla  Life,access           Pr      2.1045
32000203 4    Sunil Fernando Tangalle accessroad            Pr      0.1824

Pl tell me the T sql server code for this query. Pl help me

Answer 1

Take a look at how to join tables together here.

Answer 2

Hope this help you :)

select  LandParces.Blockid,LandParces.PNo,LandParces.Name,LandParces.Address,Encumbrances.Encumbrance
    ,Owner.StorP,Owner.Hec
    from  LandParces 
    inner join Encumbrances 
    inner join Owner
on dbo.Owner.Blockid = Encumbrances.Blockid
 on LandParces.Blockid =Encumbrances.Blockid

you can try this other joins also .

Answer 3

Try the following query:

select  LandParces.Blockid,LandParces.PNo,LandParces.Name,LandParces.Address, 
(
 SELECT  CASE WHEN ROW_NUMBER() OVER (ORDER BY Blockid) > 1 THEN ', ' ELSE '' END + Encumbrance
FROM    Encumbrances qi
        WHERE   qi.Blockid=  LandParces.Blockid 
        FOR XML PATH('')
)
 as Encumbrance,Owner.StorP,Owner.Hec
    from  LandParces 
    inner join Owner
on LandParces.Blockid =Owner.Blockid

This query is for sqlserver 2005+