Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004
    Posts
    4

    Unanswered: Tricky SQL - need help

    I have 2 tables joined together by the IDs, People and the pets they
    own

    PEOPLE
    ID NAME
    1 JohnSMith
    2 JaneDoe

    PETS
    ID PET
    1 Dog
    2 Cat
    2 Hamster
    2 Hamster
    2 Fish

    I have create another where the PETS are in one column separated by
    semi-colons and removing the dups

    NEW TABLE
    ID NAME ALLPETS
    1 JohnSmith Dog
    2 JaneDoe Cat;Hamster;Fish

    What is the best way to do it? The only way I can think of is to run
    an update where it checks to see if the value already exists

    THanks!

  2. #2
    Join Date
    Nov 2004
    Posts
    6


    Here's one approach. It asssumes your 2 tables are normalized (I couldn't tell from your example).

    CREATE TABLE ##myTable
    (
    PersonName varchar(50),
    PetTypes varchar(1024) NULL
    )

    DECLARE@PersonName varchar(50)
    DECLARE @PetType varchar(20)
    DECLARE @LastPersonName varchar(50)

    DECLARE @sql varchar(1024)

    DECLARE myCursor CURSOR
    READ_ONLY
    FOR

    SELECT tblPersons.PersonName, tblPets.PetType
    FROM tblPersons
    JOIN tblPets ON tblPersons.PetID = tblPets.PetID
    ORDER BY 1, 2

    OPEN myCursor
    FETCH NEXT FROM myCursor INTO @PersonName, @PetType
    SET @LastPersonName = ''

    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF @LastPersonName != @PersonName
    BEGIN
    SET @sql = LEFT(@sql, LEN(@sql) - 1) + ''') '
    EXEC (@sql)
    -- PRINT @sql

    SET @sql = ''
    SET @sql = @sql + 'INSERT INTO ##myTable '
    SET @sql = @sql + '(PersonName, PetTypes) '
    SET @sql = @sql + 'VALUES ('''
    SET @sql = @sql + @PersonName + ''', '''
    END

    SET @sql = @sql + '' + @PetType + '; '

    SELECT @LastPersonName = @PersonName

    FETCH NEXT FROM myCursor INTO @PersonName, @PetType
    END

    SET @sql = LEFT(@sql, LEN(@sql) - 1) + ''') '
    EXEC (@sql)
    -- PRINT @sql

    CLOSE myCursor
    DEALLOCATE myCursor

    SELECT *
    FROM ##myTable

    DROP TABLE ##myTable
    GO

    The result from my test...

    PersonName PetTypes
    ---------- ----------------------------------
    Alice Cat; Dog; Hamster; Parakeet; Snake
    John Cat; Cat; Snake
    Mary Dog; Parakeet; Snake
    Tom Parakeet

    (4 row(s) affected)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •