Creating dynamic concatenated strings in SQL Server 2008?

Question

I have data in the form of two keys that need to be combined based on a commonality between the two. In other words, I want to create a long string of combined pairs for every instance that it is shared. Please refer to the table below for reference and examples:

What my data looks like:

 KEY1 | KEY2 
 ===========
   1     A
   1     B
   1     C
   2     A
   2     F
   3     D
   3     G
   3     H

How I want to create new columns:

  KEY1 | KEY2 | NEW KEY1 | NEW KEY2
  =================================
    1     A     1_A_B_C     A_1_2
    1     B     1_A_B_C      B_1
    1     C     1_A_B_C      C_1
    2     A      2_A_F      A_1_2
    2     F      2_A_F       F_2
    3     D     3_D_G_H      D_3
    3     G     3_D_G_H      G_3
    3     H     3_D_G_H      H_3

Note that in the example where KEY1 = 1, the NEW KEY1 becomes a string of every instance of KEY2 associated with KEY1. The same would be done then for KEY2. Please note that I am using SQL Server 2008.

Thank you.

A variation of this question was answered yesterday http://stackoverflow.com/questions/32485587/query-with-multiple-line-results-in-only-one-line/32486115#32486115. You can skip the GROUP BY then my answer from yesterday will work. — cdonner, Sep 10 '15 at 17:18

FutbolFan · Accepted Answer · 2015-09-10T18:01:14.997

Your logic for NEW KEY2 is quite unclear. But, if you are looking to concatenate two columns to create the new key columns, you could do something like this:

Sample Table:

create table mytable ( KEY1 int, KEY2 varchar(1));

insert into mytable values
   (1, 'A'),
   (1, 'B'),
   (1, 'C'),
   (2, 'A'),
   (2, 'F'),
   (3, 'D'),
   (3, 'G'),
   (3, 'H');

Query:

select q1.key1,
       q1.key2,
       cast(q1.key1 as varchar(5)) + '_' + replace(q1.newkey1,',','_') as new_key1,
       q1.key2 + '_' + replace(q1.newkey2,',','_') as new_key2
 from
(SELECT key1
        ,key2
        ,STUFF((
            SELECT ',' + t1.key2
            FROM mytable T1
            WHERE T1.Key1 = T2.Key1
            FOR XML PATH('')
            ), 1, 1, '') as newkey1
       ,STUFF((
            SELECT ',' + cast(t3.key1 as varchar(5))
            FROM mytable T3
            WHERE T3.Key2 = T2.Key2
            FOR XML PATH('')
            ), 1, 1, '') as newkey2
FROM mytable T2) q1

Result:

+------+------+----------+----------+
| key1 | key2 | new_key1 | new_key2 |
+------+------+----------+----------+
|    1 | A    | 1_A_B_C  | A_1_2    |
|    1 | B    | 1_A_B_C  | B_1      |
|    1 | C    | 1_A_B_C  | C_1      |
|    2 | A    | 2_A_F    | A_1_2    |
|    2 | F    | 2_A_F    | F_2      |
|    3 | D    | 3_D_G_H  | D_3      |
|    3 | G    | 3_D_G_H  | G_3      |
|    3 | H    | 3_D_G_H  | H_3      |
+------+------+----------+----------+

SQL Server Demo

Hey thank you for the reply! That was very helpful. As for New Key 2, it's essentially the same logic as New Key 1, but just flipped around. Would you be able to edit your query to reflect that? Thank you! — vdiddy, Sep 10 '15 at 17:48
Sure! So, for e.g. new_key2 for the first row would be `A_B_C_1` instead of `A_1`? — FutbolFan, Sep 10 '15 at 17:51
Hmm no. Sorry I am having trouble explaining it. Just the way that we are grouping everything from Key1 with Key2 for New Key 1, we would group everything from Key2 with everything in Key1 for New Key 2. So when Key2 = A, Key1 = {1,2} so we want A_1_2 for both of the rows where Key1 = {1,2}. I think since they are not in order it may be a little difficult to understand. — vdiddy, Sep 10 '15 at 17:52
Yes it does! Thank you again! Sincerely appreciate the quick and useful guidance. — vdiddy, Sep 10 '15 at 18:03

Creating dynamic concatenated strings in SQL Server 2008?

1 Answers1