Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Is there a best way to combine data into a single output field RE: Is there a best way to combine data into a single output field

  • April 28, 2017 at 11:09 pm

    #1940474

    Rod Harten - Friday, April 28, 2017 6:38 PM

    I need to create output that takes a list of names and combines them into one field. Here is an example of the output:Name    States

    Bob       Ohio, Iowa, Alaska
    Sue       Idaho, Iowa
    Jane      Alaska, Hawaii, Idaho, Ohio

    The tables are well-formed (see below) and I can brute force a solution but am curious if there is a better, preferred way. I have two questions.

    1) Is there a recommended way for creating this sort of output?
    2) Is there a name for this sort of process? I can't think of one, which makes it difficult to search for a recommended solution.

    Thank you for your time and attention,

    Rod

    --===============================================================

    CREATE TABLE #names (
        NameID int,
        name varchar(4));

    INSERT INTO #names
    VALUES (1, 'Bob'), (2, 'Sue'), (3, 'Jane');

    ----

    CREATE TABLE #states (
      StateID int,
        State varchar(6));

    INSERT INTO #states
    VALUES (1, 'Ohio'), (2, 'Iowa'), (3, 'Hawaii'), (4, 'Alaska'), (5, 'Idaho');

    ----

    CREATE TABLE #name_states (
      NameID int,
        StateID int);

    INSERT INTO #name_states
    VALUES (1, 1), (1, 2), (1, 4), (2, 5), (2, 2), (3, 4), (3, 3), (3, 5), (3, 1);

    I think this will give you what you're looking for. 

    WITH cteData AS (
      SELECT n.Name, s.State
        FROM #names n
          INNER JOIN #name_states ns ON ns.NameID = n.NameID
             INNER JOIN #states s ON ns.StateID = s.StateID
    ),
    cteNames AS (
      SELECT DISTINCT Name
         FROM #names
    )
    SELECT Name,
       StatesList = STUFF((SELECT ',' + d.State
                             FROM cteData d
                             WHERE d.Name = cteNames.Name
                             ORDER BY d.State
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
       FROM cteNames
       ORDER BY Name;

    There are many approaches to creating a delimited list, but Wayne Sheffield's article at http://www.sqlservercentral.com/articles/comma+separated+list/71700/ is the approach I prefer.  This is the technique above.

    Edit: Please forgive the indenting of the code, but I still haven't gotten the knack of the new SQL editor on SSC.  I tried to get it close.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions