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 stackoverflow.com/questions/32485587/…. You can skip the GROUP BY then my answer from yesterday will work. — cdonner
– cdonner, Commented Sep 10, 2015 at 17:18

FutbolFan · Accepted Answer · 2015-09-10 18:01:14Z

1

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

edited Sep 10, 2015 at 18:01

answered Sep 10, 2015 at 17:42

FutbolFan

13.9k3 gold badges27 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

vdiddy Over a year ago

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!

FutbolFan Over a year ago

Sure! So, for e.g. new_key2 for the first row would be A_B_C_1 instead of A_1?

vdiddy Over a year ago

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 Over a year ago

Yes it does! Thank you again! Sincerely appreciate the quick and useful guidance.

Collectives™ on Stack Overflow

Creating dynamic concatenated strings in SQL Server 2008?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related