Set-based solution for strings (a.k.a. Kill a Loop / No RBAR)?

  • To all the SQL Pros out there...

    For this issue, I am attempting to implement set-based thinking. However, I'm not sure how to get around using variables with a while loop to do this using string values. I've done it using aggregates with numeric fields, but was wondering if there was a more elegant solution than this.

    I've done plenty of searches for this, so if you have related article links, that would also be greatly appreciated.

    Given the table:

    table_id field_id string

    -------- -------- ------

    1 1 A

    2 1 B

    3 1 C

    4 2 D

    5 2 E

    6 3 F

    7 3 G

    8 3 H

    9 3 I

    I want to insert the following results into another table:

    field_id string

    -------- ------

    1 Result: A, B, C

    2 Result: D, E

    3 Result: F, G, H, I

    Here is my current solution. I am using temp tables instead of table variable, so if a better solution can be gained that way I am flexible on the implementation.

    SQL Code:

    -- Declare the variables

    DECLARE @tblInput TABLE

    ( table_id INT,

    field_id INT,

    sequence_id INT,

    value CHAR(1) );

    DECLARE @tblOutput TABLE

    ( field_id INT,

    string VARCHAR(50) );

    DECLARE @intIndex INT,

    @intFieldID INT,

    @intReturnString VARCHAR(50),

    @intSequenceID INT;

    -- Set the default variable values

    SET @intIndex = 1;

    SET @intFieldID = 0;

    SET @intReturnString = '';

    SET @intSequenceID = 0;

    /* STEP 1: Populate the Input table */

    INSERT INTO @tblInput

    ( table_id, field_id, value )

    SELECT 1, 1, 'A'

    UNION ALL

    SELECT 2, 1, 'B'

    UNION ALL

    SELECT 3, 1, 'C'

    UNION ALL

    SELECT 4, 2, 'D'

    UNION ALL

    SELECT 5, 2, 'E'

    UNION ALL

    SELECT 6, 3, 'F'

    UNION ALL

    SELECT 7, 3, 'G'

    UNION ALL

    SELECT 8, 3, 'H'

    UNION ALL

    SELECT 9, 3, 'I';

    -- Set the sequence id, using the request_workflow_id as a grouping

    UPDATE @tblInput

    SET @intSequenceID = sequence_id =

    CASE

    WHEN @intFieldID = field_id THEN @intSequenceID + 1

    ELSE 1

    END,

    @intFieldID = field_id;

    -- Review the Input table

    SELECT * FROM @tblInput;

    /* STEP 2: Build the output table */

    -- This loop adds the string value for each field_id to the Output table

    WHILE @intIndex <= (SELECT MAX(field_id) FROM @tblInput)

    BEGIN

    -- Build the assignment string for each request workflow

    SELECT @intFieldID = field_id,

    @intReturnString = CASE sequence_id

    WHEN 1 THEN 'Result: ' + value

    ELSE @intReturnString + ', ' + value

    END

    FROM @tblInput

    WHERE field_id = @intIndex;

    -- Insert the assignment string for this request workflow

    INSERT INTO @tblOutput(field_id, string)

    VALUES (@intFieldID, @intReturnString);

    SET @intReturnString = '';

    SET @intIndex = @intIndex + 1;

    END;

    -- Review the Output table

    SELECT * from @tblOutput

  • If you remove the commas from the examples, the following article explains one fast and set based way to do this.

    http://www.sqlservercentral.com/articles/comma+separated+list/71700/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • If you mean something like this:

    DECLARE @tblInput TABLE

    ( table_id INT,

    field_id INT,

    sequence_id INT,

    value CHAR(1) );

    /* STEP 1: Populate the Input table */

    INSERT INTO @tblInput

    ( table_id, field_id, value )

    SELECT 1, 1, 'A'

    UNION ALL

    SELECT 2, 1, 'B'

    UNION ALL

    SELECT 3, 1, 'C'

    UNION ALL

    SELECT 4, 2, 'D'

    UNION ALL

    SELECT 5, 2, 'E'

    UNION ALL

    SELECT 6, 3, 'F'

    UNION ALL

    SELECT 7, 3, 'G'

    UNION ALL

    SELECT 8, 3, 'H'

    UNION ALL

    SELECT 9, 3, 'I';

    with BaseData as (

    select distinct

    field_id

    from

    @tblInput

    )

    select

    bd.field_id,

    stuff((select ',' + bd1.value

    from @tblInput bd1

    where bd.field_id = bd1.field_id

    for xml path(''),type).value('.','varchar(max)'),1,1,'') as ConCatField

    from

    BaseData bd;

  • Thank you, Jeff and Lynn for pointing me in the right direction. Now I'm off to brush up on my XML...:cool:

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply