Sql Query for multiple tables

Question

I Used SQL 2000 and database have following tables

LandParcel (Table Name)
BlockID  ParcelNo NameofOnwe
11001    1056     Chandana
11001    1078     Sisil
11001    1158     Kumara
11078    105      SK
11078    245      Shantha

Actions (Table)
Blockid  ParcelNo  ActionTaken
11001    1056      Received
11001    1078      Received
11001    1158      Received
11078    105       Received
11078    245       Received
11001    1056      Send To LR
11001    1078      Send to LR
11078    105       Send To LT

I want the following query

Blockid  ActionTaken   ParcelNos
11001    Received      1056, 1078, 1158
11078    Received      105, 245
11001    Send To LR    1056, 1078
11078    Send To LR    105

Pl help me Chandana

Answer 1

Refer this post http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx

Answer 2

I don't like cursors much but I cold not find other solution. I might try later doing this without cursors.

 /* Sample data.*/


create TABLE #LandParcel (BlockID INT,  ParcelNo INT, NameofOnwe VARCHAR(50))

insert INTo #LandParcel select 11001   , 1056 ,   'Chandana'
insert INTo #LandParcel select 11001  ,  1078   , 'Sisil'
insert INTo #LandParcel select 11001   , 1158  ,  'Kumara'
insert INTo #LandParcel select 11078   , 105  ,   'SK'
insert INTo #LandParcel select 11078  ,  245   ,   'Shantha'


CREATE TABLE #Actions (Blockid INT, ParcelNo  INT, ActionTaken VARCHAR(50))

insert INTo #Actions select 11001 ,   1056    ,'Received'
insert INTo #Actions select 11001  ,  1078   , 'Received'
insert INTo #Actions select 11001   , 1158  ,  'Received'
insert INTo #Actions select 11078   , 105    , 'Received'
insert INTo #Actions select 11078  ,  245    , 'Received'
insert INTo #Actions select 11001  ,  1056    ,  'Send To LR'
insert INTo #Actions select 11001   , 1078    ,  'Send to LR'
insert INTo #Actions select 11078  ,  105     ,  'Send To LT'

/* End sample data */

/* Update query*/

CREATE TABLE #temp (BlockId INT, ActionTaken VARCHAR(50), ParcelNumbers VARCHAR(100))

INSERT  INTO #temp
SELECT  l.blockid, a.ActionTaken, NULL
FROM    #LandParcel l INNER JOIN #Actions a on l.blockid = a.blockid
GROUP BY l.blockid, a.actiontaken

DECLARE @blockId INT
DECLARE @actionTaken VARCHAR(50)
DECLARE @parcel VARCHAR(100)

SET @parcel = ''

DECLARE @cursorParcel CURSOR
    SET     @cursorParcel = CURSOR FAST_FORWARD
    FOR
    SELECT  blockid, ActionTaken FROM #temp

    OPEN    @cursorParcel

    FETCH   NEXT    FROM    @cursorParcel
    INTO    @blockId,
            @actionTaken
    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT @parcel = CASE @parcel 
                WHEN '' THEN convert(VARCHAR(10), a.ParcelNo )
                ELSE @parcel + ', ' + convert(VARCHAR(10), a.ParcelNo ) 
                END
        from #temp t INNER JOIN #Actions a on t.blockid = a.blockid and t.actiontaken = a.actiontaken
        where t.BlockId = @blockId
        AND     t.ActionTaken = @actionTaken

        UPDATE  #temp SET ParcelNumbers = @parcel
        WHERE   BlockId = @blockId
        AND     ActionTaken = @actionTaken

        SET @blockId        = NULL
        SET @actionTaken    = NULL
        SET @parcel         = ''

        FETCH   NEXT    FROM    @cursorParcel
        INTO    @blockId,
                @actionTaken

    END


CLOSE       @cursorParcel
DEALLOCATE  @cursorParcel

SELECT * FROM #temp

DROP TABLE #temp
DROP TABLE #LandParcel
DROP TABLE #Actions

Answer 3

You can use FOR XML RAW from SQL Server 2000 to simulate the FOR XML PATH behavior of SQL Server 2005:

SELECT
  a.BlockID,
  a.ActionTaken,
  REPLACE(
  REPLACE(
  REPLACE(
  (SELECT ParcelNo
   FROM Actions
   WHERE BlockID = a.BlockID
   AND ActionTaken = a.ActionTaken
   ORDER BY ParcelNo
   FOR XML RAW), '"/><row ParcelNo="', ', '),
   '<row ParcelNo="', ''),
   '"/>', '')  AS ParcelNos
FROM Actions a 
INNER JOIN LandParcel l 
  ON a.BlockID = l.BlockID
  AND a.ParcelNo = l.ParcelNo
GROUP BY 
  a.BlockID,
  a.ActionTaken
ORDER BY 
  a.ActionTaken