CODEWITHSUNDEEP
sqlsql-server-2008
vdiddy
vdiddy

Reputation: 85

Creating dynamic concatenated strings in SQL Server 2008?

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.

Upvotes: 0

Views: 34

Answers (1)

FutbolFan
FutbolFan

Reputation: 13713

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

Upvotes: 1

Related Questions