none
Update Column by "aggregating" text from another table column

    Question

  • Hi,

    SUPPOSE I HAVE THESE 2 TABLES:

    IF OBJECT_ID('dbo.HUC824K', 'U') IS NOT NULL
     DROP TABLE dbo.HUC824K;

    CREATE TABLE dbo.HUC824K(
      FIPS_C [varchar] (254) NULL,
      HUC8 [varchar] (8) NULL);
     
    INSERT INTO dbo.HUC824K(FIPS_C, HUC8)
    VALUES
    ('01010002', '23003 23021 23025'),
    ('01010003', '23003'),
    ('01010004', '23003 23019 23021');

    IF OBJECT_ID('dbo.NETBLUE', 'U') IS NOT NULL
     DROP TABLE dbo.NETBLUE;

    CREATE TABLE dbo.NETBLUE(
      FIPS [varchar] (50) NULL,
      HUC8 [varchar] (8) NULL);

    INSERT INTO dbo.NETBLUE(FIPS, HUC8)
    VALUES
    ('23003', NULL),
    ('23021', NULL),
    ('23025', NULL),
    ('23019', NULL);

    What is the correct query that would result in HUC8 values for NetBlue table as follows:

    FIPS         HUC8
    '23003'    '01010002 01010003 01010004'  --space-delimited
    '23019'    '01010004'
    '23021'    '01010002 01010004'
    '23025'    '01010002'

    Appreciate your assistance.


    Marilyn Gambone

    Monday, August 26, 2013 8:14 PM

Answers

  • Try this:

    Update NETBLUE
    Set HUC8=STUFF((     SELECT ' '+HUC824K.FIPS_C
                                    FROM HUC824K
                                    WHERE ' '+HUC824K.HUC8+' ' like '% '+   NETBLUE.FIPS +' %'
                                    FOR XML PATH('')), 1, 1, '' )
             

    FROM  NETBLUE

    Select *
    From NETBLUE

    ALSO MAKE SURE COLUMN WIDTH FOR HUC8 in Table HUC824K and NETBLUE SHOULD BE SUFFICIENT ENOUGH TO HOLD VALUE LIKE 01010002 01010003 01010004. MAKE IT 1000 FOR TEST.


    • Edited by Taherul673 Monday, August 26, 2013 8:46 PM
    • Proposed as answer by Taherul673 Monday, August 26, 2013 8:58 PM
    • Marked as answer by deskcheck1 Tuesday, August 27, 2013 7:55 PM
    Monday, August 26, 2013 8:45 PM

All replies

  • Try this:

    Update NETBLUE
    Set HUC8=STUFF((     SELECT ' '+HUC824K.FIPS_C
                                    FROM HUC824K
                                    WHERE ' '+HUC824K.HUC8+' ' like '% '+   NETBLUE.FIPS +' %'
                                    FOR XML PATH('')), 1, 1, '' )
             

    FROM  NETBLUE

    Select *
    From NETBLUE

    ALSO MAKE SURE COLUMN WIDTH FOR HUC8 in Table HUC824K and NETBLUE SHOULD BE SUFFICIENT ENOUGH TO HOLD VALUE LIKE 01010002 01010003 01010004. MAKE IT 1000 FOR TEST.


    • Edited by Taherul673 Monday, August 26, 2013 8:46 PM
    • Proposed as answer by Taherul673 Monday, August 26, 2013 8:58 PM
    • Marked as answer by deskcheck1 Tuesday, August 27, 2013 7:55 PM
    Monday, August 26, 2013 8:45 PM
  • Thanks.  This works for me.

    Marilyn Gambone

    Tuesday, August 27, 2013 7:55 PM