Concatenation of columns with no fixed number of columns in SQL

Multi tool use
I am trying to concatenate a bunch of columns in SQL.
The problem is that there is no set number of columns and therefore could have to concatenate 18 columns together in one run and 30 the next.
The table structures look like :
concatbase:
ID | planprefix | plannumber | ConcatField_1 | ConcatField_2 | ConcatField_3 | ....
----+------------+------------+---------------+---------------+---------------+ ....
1 | p | 11 | 100-150 | 300 | 302 | ....
1 | P | 111 | 101 | NULL | NULL | ....
1 | P | 2222 | 600-908 | 1010 | NULL | ....
4 | D | 33333 | 400-406 | NULL | NULL | ....
5 | D | 444444 | 300 | NULL | NULL | ....
6 | p | 19 | 200 | 300-308 | 400 | ....
Table RPD_STAGING (and expected result given ID 1 sample data)
Rank | ID | semi_rp |
--------+-----------+---------------------------+
1 | 1 | 100-150 & 300 & 302 P11 |
2 | 1 | 101 P11 |
3 | 1 | 608-908 & 1010 P2222 |
I am using this SQL statement to concatenate this together:
SELECT DISTINCT
DENSE_RANK() OVER (PARTITION BY propertyid ORDER BY plannumber) AS rank,
propertyid,
CASE WHEN ConcatField_1 IS NULL THEN ' ' + planprefix + plannumber ELSE ConcatField_1 END +
CASE WHEN ConcatField_2 IS NULL AND ConcatField_1 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_2 is null and ConcatField_1 IS NULL then '' ELSE ' & ' + ConcatField_2 END +
CASE WHEN ConcatField_3 IS NULL AND ConcatField_2 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_3 is null and ConcatField_2 IS NULL then '' ELSE ' & ' + ConcatField_3 END +
CASE WHEN ConcatField_4 IS NULL AND ConcatField_3 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_4 is null and ConcatField_3 IS NULL then '' ELSE ' & ' + ConcatField_4 END +
CASE WHEN ConcatField_5 IS NULL AND ConcatField_4 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_5 is null and ConcatField_4 IS NULL then '' ELSE ' & ' + ConcatField_5 END +
CASE WHEN ConcatField_6 IS NULL AND ConcatField_5 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_6 is null and ConcatField_5 IS NULL then '' ELSE ' & ' + ConcatField_6 END +
CASE WHEN ConcatField_7 IS NULL AND ConcatField_6 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_7 is null and ConcatField_6 IS NULL then '' ELSE ' & ' + ConcatField_7 END +
CASE WHEN ConcatField_8 IS NULL AND ConcatField_7 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_8 is null and ConcatField_7 IS NULL then '' ELSE ' & ' + ConcatField_8 END +
CASE WHEN ConcatField_9 IS NULL AND ConcatField_8 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_9 is null and ConcatField_8 IS NULL then '' ELSE ' & ' + ConcatField_9 END +
CASE WHEN ConcatField_10 IS NULL AND ConcatField_9 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_10 is null and ConcatField_9 IS NULL then '' ELSE ' & ' + ConcatField_10 END +
CASE WHEN ConcatField_11 IS NULL AND ConcatField_10 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_11 is null and ConcatField_10 IS NULL then '' ELSE ' & ' + ConcatField_11 END +
CASE WHEN ConcatField_12 IS NULL AND ConcatField_11 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_12 is null and ConcatField_11 IS NULL then '' ELSE ' & ' + ConcatField_12 END +
CASE WHEN ConcatField_13 IS NULL AND ConcatField_12 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_13 is null and ConcatField_12 IS NULL then '' ELSE ' & ' + ConcatField_13 END +
CASE WHEN ConcatField_14 IS NULL AND ConcatField_13 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_14 is null and ConcatField_13 IS NULL then '' ELSE ' & ' + ConcatField_14 END +
CASE WHEN ConcatField_15 IS NULL AND ConcatField_14 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_15 is null and ConcatField_14 IS NULL then '' ELSE ' & ' + ConcatField_15 END +
CASE WHEN ConcatField_16 IS NULL AND ConcatField_15 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_16 is null and ConcatField_15 IS NULL then '' ELSE ' & ' + ConcatField_16 END +
CASE WHEN ConcatField_17 IS NULL AND ConcatField_16 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_17 is null and ConcatField_16 IS NULL then '' ELSE ' & ' + ConcatField_17 END +
CASE WHEN ConcatField_18 IS NULL AND ConcatField_17 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_18 is null and ConcatField_17 IS NULL then '' ELSE ' & ' + ConcatField_18 END AS semi_rpd
INTO
#RPD_STAGING
FROM
#concat_base
Can anyone assist me in creating a solution that could deal with any number of ConcatFields?
If possible I would like to stick to SQL, however if necessary I am open to using other tools. If you need any further information please let me know.
sql

add a comment |
I am trying to concatenate a bunch of columns in SQL.
The problem is that there is no set number of columns and therefore could have to concatenate 18 columns together in one run and 30 the next.
The table structures look like :
concatbase:
ID | planprefix | plannumber | ConcatField_1 | ConcatField_2 | ConcatField_3 | ....
----+------------+------------+---------------+---------------+---------------+ ....
1 | p | 11 | 100-150 | 300 | 302 | ....
1 | P | 111 | 101 | NULL | NULL | ....
1 | P | 2222 | 600-908 | 1010 | NULL | ....
4 | D | 33333 | 400-406 | NULL | NULL | ....
5 | D | 444444 | 300 | NULL | NULL | ....
6 | p | 19 | 200 | 300-308 | 400 | ....
Table RPD_STAGING (and expected result given ID 1 sample data)
Rank | ID | semi_rp |
--------+-----------+---------------------------+
1 | 1 | 100-150 & 300 & 302 P11 |
2 | 1 | 101 P11 |
3 | 1 | 608-908 & 1010 P2222 |
I am using this SQL statement to concatenate this together:
SELECT DISTINCT
DENSE_RANK() OVER (PARTITION BY propertyid ORDER BY plannumber) AS rank,
propertyid,
CASE WHEN ConcatField_1 IS NULL THEN ' ' + planprefix + plannumber ELSE ConcatField_1 END +
CASE WHEN ConcatField_2 IS NULL AND ConcatField_1 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_2 is null and ConcatField_1 IS NULL then '' ELSE ' & ' + ConcatField_2 END +
CASE WHEN ConcatField_3 IS NULL AND ConcatField_2 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_3 is null and ConcatField_2 IS NULL then '' ELSE ' & ' + ConcatField_3 END +
CASE WHEN ConcatField_4 IS NULL AND ConcatField_3 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_4 is null and ConcatField_3 IS NULL then '' ELSE ' & ' + ConcatField_4 END +
CASE WHEN ConcatField_5 IS NULL AND ConcatField_4 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_5 is null and ConcatField_4 IS NULL then '' ELSE ' & ' + ConcatField_5 END +
CASE WHEN ConcatField_6 IS NULL AND ConcatField_5 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_6 is null and ConcatField_5 IS NULL then '' ELSE ' & ' + ConcatField_6 END +
CASE WHEN ConcatField_7 IS NULL AND ConcatField_6 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_7 is null and ConcatField_6 IS NULL then '' ELSE ' & ' + ConcatField_7 END +
CASE WHEN ConcatField_8 IS NULL AND ConcatField_7 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_8 is null and ConcatField_7 IS NULL then '' ELSE ' & ' + ConcatField_8 END +
CASE WHEN ConcatField_9 IS NULL AND ConcatField_8 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_9 is null and ConcatField_8 IS NULL then '' ELSE ' & ' + ConcatField_9 END +
CASE WHEN ConcatField_10 IS NULL AND ConcatField_9 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_10 is null and ConcatField_9 IS NULL then '' ELSE ' & ' + ConcatField_10 END +
CASE WHEN ConcatField_11 IS NULL AND ConcatField_10 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_11 is null and ConcatField_10 IS NULL then '' ELSE ' & ' + ConcatField_11 END +
CASE WHEN ConcatField_12 IS NULL AND ConcatField_11 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_12 is null and ConcatField_11 IS NULL then '' ELSE ' & ' + ConcatField_12 END +
CASE WHEN ConcatField_13 IS NULL AND ConcatField_12 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_13 is null and ConcatField_12 IS NULL then '' ELSE ' & ' + ConcatField_13 END +
CASE WHEN ConcatField_14 IS NULL AND ConcatField_13 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_14 is null and ConcatField_13 IS NULL then '' ELSE ' & ' + ConcatField_14 END +
CASE WHEN ConcatField_15 IS NULL AND ConcatField_14 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_15 is null and ConcatField_14 IS NULL then '' ELSE ' & ' + ConcatField_15 END +
CASE WHEN ConcatField_16 IS NULL AND ConcatField_15 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_16 is null and ConcatField_15 IS NULL then '' ELSE ' & ' + ConcatField_16 END +
CASE WHEN ConcatField_17 IS NULL AND ConcatField_16 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_17 is null and ConcatField_16 IS NULL then '' ELSE ' & ' + ConcatField_17 END +
CASE WHEN ConcatField_18 IS NULL AND ConcatField_17 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_18 is null and ConcatField_17 IS NULL then '' ELSE ' & ' + ConcatField_18 END AS semi_rpd
INTO
#RPD_STAGING
FROM
#concat_base
Can anyone assist me in creating a solution that could deal with any number of ConcatFields?
If possible I would like to stick to SQL, however if necessary I am open to using other tools. If you need any further information please let me know.
sql

You can build a dynamic SQL query maybe. By concatenating SQL commands as string pieces for a final statement then execute it with sp_executesql command as shown in sample kodyaz.com/articles/…
– Eralper
Jan 2 at 5:44
I am familiar with dynamic sql statements, however not sure exactly how to use them in this case. Could you give a quick example of how I could use it to write one for this problem?
– Matthew Hancock
Jan 2 at 6:14
You can query the SYS.TABLE_COLUMNS system view and search for the maximum number for field names like 'ConcatField_%'. When you have this information, you can add that many of following sql code fragment " CASE WHEN ConcatField_17 IS NULL AND ConcatField_16 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_17 is null and ConcatField_16 IS NULL then '' ELSE ' & ' + ConcatField_17 END + "
– Eralper
Jan 2 at 6:17
@MatthewHancock . . . A table has a fixed number of columns. I don't understand your data model, but you have a problem if the number of columns in a table can change.
– Gordon Linoff
Jan 2 at 12:46
add a comment |
I am trying to concatenate a bunch of columns in SQL.
The problem is that there is no set number of columns and therefore could have to concatenate 18 columns together in one run and 30 the next.
The table structures look like :
concatbase:
ID | planprefix | plannumber | ConcatField_1 | ConcatField_2 | ConcatField_3 | ....
----+------------+------------+---------------+---------------+---------------+ ....
1 | p | 11 | 100-150 | 300 | 302 | ....
1 | P | 111 | 101 | NULL | NULL | ....
1 | P | 2222 | 600-908 | 1010 | NULL | ....
4 | D | 33333 | 400-406 | NULL | NULL | ....
5 | D | 444444 | 300 | NULL | NULL | ....
6 | p | 19 | 200 | 300-308 | 400 | ....
Table RPD_STAGING (and expected result given ID 1 sample data)
Rank | ID | semi_rp |
--------+-----------+---------------------------+
1 | 1 | 100-150 & 300 & 302 P11 |
2 | 1 | 101 P11 |
3 | 1 | 608-908 & 1010 P2222 |
I am using this SQL statement to concatenate this together:
SELECT DISTINCT
DENSE_RANK() OVER (PARTITION BY propertyid ORDER BY plannumber) AS rank,
propertyid,
CASE WHEN ConcatField_1 IS NULL THEN ' ' + planprefix + plannumber ELSE ConcatField_1 END +
CASE WHEN ConcatField_2 IS NULL AND ConcatField_1 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_2 is null and ConcatField_1 IS NULL then '' ELSE ' & ' + ConcatField_2 END +
CASE WHEN ConcatField_3 IS NULL AND ConcatField_2 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_3 is null and ConcatField_2 IS NULL then '' ELSE ' & ' + ConcatField_3 END +
CASE WHEN ConcatField_4 IS NULL AND ConcatField_3 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_4 is null and ConcatField_3 IS NULL then '' ELSE ' & ' + ConcatField_4 END +
CASE WHEN ConcatField_5 IS NULL AND ConcatField_4 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_5 is null and ConcatField_4 IS NULL then '' ELSE ' & ' + ConcatField_5 END +
CASE WHEN ConcatField_6 IS NULL AND ConcatField_5 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_6 is null and ConcatField_5 IS NULL then '' ELSE ' & ' + ConcatField_6 END +
CASE WHEN ConcatField_7 IS NULL AND ConcatField_6 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_7 is null and ConcatField_6 IS NULL then '' ELSE ' & ' + ConcatField_7 END +
CASE WHEN ConcatField_8 IS NULL AND ConcatField_7 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_8 is null and ConcatField_7 IS NULL then '' ELSE ' & ' + ConcatField_8 END +
CASE WHEN ConcatField_9 IS NULL AND ConcatField_8 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_9 is null and ConcatField_8 IS NULL then '' ELSE ' & ' + ConcatField_9 END +
CASE WHEN ConcatField_10 IS NULL AND ConcatField_9 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_10 is null and ConcatField_9 IS NULL then '' ELSE ' & ' + ConcatField_10 END +
CASE WHEN ConcatField_11 IS NULL AND ConcatField_10 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_11 is null and ConcatField_10 IS NULL then '' ELSE ' & ' + ConcatField_11 END +
CASE WHEN ConcatField_12 IS NULL AND ConcatField_11 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_12 is null and ConcatField_11 IS NULL then '' ELSE ' & ' + ConcatField_12 END +
CASE WHEN ConcatField_13 IS NULL AND ConcatField_12 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_13 is null and ConcatField_12 IS NULL then '' ELSE ' & ' + ConcatField_13 END +
CASE WHEN ConcatField_14 IS NULL AND ConcatField_13 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_14 is null and ConcatField_13 IS NULL then '' ELSE ' & ' + ConcatField_14 END +
CASE WHEN ConcatField_15 IS NULL AND ConcatField_14 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_15 is null and ConcatField_14 IS NULL then '' ELSE ' & ' + ConcatField_15 END +
CASE WHEN ConcatField_16 IS NULL AND ConcatField_15 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_16 is null and ConcatField_15 IS NULL then '' ELSE ' & ' + ConcatField_16 END +
CASE WHEN ConcatField_17 IS NULL AND ConcatField_16 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_17 is null and ConcatField_16 IS NULL then '' ELSE ' & ' + ConcatField_17 END +
CASE WHEN ConcatField_18 IS NULL AND ConcatField_17 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_18 is null and ConcatField_17 IS NULL then '' ELSE ' & ' + ConcatField_18 END AS semi_rpd
INTO
#RPD_STAGING
FROM
#concat_base
Can anyone assist me in creating a solution that could deal with any number of ConcatFields?
If possible I would like to stick to SQL, however if necessary I am open to using other tools. If you need any further information please let me know.
sql

I am trying to concatenate a bunch of columns in SQL.
The problem is that there is no set number of columns and therefore could have to concatenate 18 columns together in one run and 30 the next.
The table structures look like :
concatbase:
ID | planprefix | plannumber | ConcatField_1 | ConcatField_2 | ConcatField_3 | ....
----+------------+------------+---------------+---------------+---------------+ ....
1 | p | 11 | 100-150 | 300 | 302 | ....
1 | P | 111 | 101 | NULL | NULL | ....
1 | P | 2222 | 600-908 | 1010 | NULL | ....
4 | D | 33333 | 400-406 | NULL | NULL | ....
5 | D | 444444 | 300 | NULL | NULL | ....
6 | p | 19 | 200 | 300-308 | 400 | ....
Table RPD_STAGING (and expected result given ID 1 sample data)
Rank | ID | semi_rp |
--------+-----------+---------------------------+
1 | 1 | 100-150 & 300 & 302 P11 |
2 | 1 | 101 P11 |
3 | 1 | 608-908 & 1010 P2222 |
I am using this SQL statement to concatenate this together:
SELECT DISTINCT
DENSE_RANK() OVER (PARTITION BY propertyid ORDER BY plannumber) AS rank,
propertyid,
CASE WHEN ConcatField_1 IS NULL THEN ' ' + planprefix + plannumber ELSE ConcatField_1 END +
CASE WHEN ConcatField_2 IS NULL AND ConcatField_1 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_2 is null and ConcatField_1 IS NULL then '' ELSE ' & ' + ConcatField_2 END +
CASE WHEN ConcatField_3 IS NULL AND ConcatField_2 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_3 is null and ConcatField_2 IS NULL then '' ELSE ' & ' + ConcatField_3 END +
CASE WHEN ConcatField_4 IS NULL AND ConcatField_3 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_4 is null and ConcatField_3 IS NULL then '' ELSE ' & ' + ConcatField_4 END +
CASE WHEN ConcatField_5 IS NULL AND ConcatField_4 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_5 is null and ConcatField_4 IS NULL then '' ELSE ' & ' + ConcatField_5 END +
CASE WHEN ConcatField_6 IS NULL AND ConcatField_5 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_6 is null and ConcatField_5 IS NULL then '' ELSE ' & ' + ConcatField_6 END +
CASE WHEN ConcatField_7 IS NULL AND ConcatField_6 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_7 is null and ConcatField_6 IS NULL then '' ELSE ' & ' + ConcatField_7 END +
CASE WHEN ConcatField_8 IS NULL AND ConcatField_7 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_8 is null and ConcatField_7 IS NULL then '' ELSE ' & ' + ConcatField_8 END +
CASE WHEN ConcatField_9 IS NULL AND ConcatField_8 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_9 is null and ConcatField_8 IS NULL then '' ELSE ' & ' + ConcatField_9 END +
CASE WHEN ConcatField_10 IS NULL AND ConcatField_9 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_10 is null and ConcatField_9 IS NULL then '' ELSE ' & ' + ConcatField_10 END +
CASE WHEN ConcatField_11 IS NULL AND ConcatField_10 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_11 is null and ConcatField_10 IS NULL then '' ELSE ' & ' + ConcatField_11 END +
CASE WHEN ConcatField_12 IS NULL AND ConcatField_11 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_12 is null and ConcatField_11 IS NULL then '' ELSE ' & ' + ConcatField_12 END +
CASE WHEN ConcatField_13 IS NULL AND ConcatField_12 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_13 is null and ConcatField_12 IS NULL then '' ELSE ' & ' + ConcatField_13 END +
CASE WHEN ConcatField_14 IS NULL AND ConcatField_13 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_14 is null and ConcatField_13 IS NULL then '' ELSE ' & ' + ConcatField_14 END +
CASE WHEN ConcatField_15 IS NULL AND ConcatField_14 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_15 is null and ConcatField_14 IS NULL then '' ELSE ' & ' + ConcatField_15 END +
CASE WHEN ConcatField_16 IS NULL AND ConcatField_15 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_16 is null and ConcatField_15 IS NULL then '' ELSE ' & ' + ConcatField_16 END +
CASE WHEN ConcatField_17 IS NULL AND ConcatField_16 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_17 is null and ConcatField_16 IS NULL then '' ELSE ' & ' + ConcatField_17 END +
CASE WHEN ConcatField_18 IS NULL AND ConcatField_17 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_18 is null and ConcatField_17 IS NULL then '' ELSE ' & ' + ConcatField_18 END AS semi_rpd
INTO
#RPD_STAGING
FROM
#concat_base
Can anyone assist me in creating a solution that could deal with any number of ConcatFields?
If possible I would like to stick to SQL, however if necessary I am open to using other tools. If you need any further information please let me know.
sql

sql

edited Jan 2 at 6:32
marc_s
580k13011181266
580k13011181266
asked Jan 2 at 4:13
Matthew HancockMatthew Hancock
12
12
You can build a dynamic SQL query maybe. By concatenating SQL commands as string pieces for a final statement then execute it with sp_executesql command as shown in sample kodyaz.com/articles/…
– Eralper
Jan 2 at 5:44
I am familiar with dynamic sql statements, however not sure exactly how to use them in this case. Could you give a quick example of how I could use it to write one for this problem?
– Matthew Hancock
Jan 2 at 6:14
You can query the SYS.TABLE_COLUMNS system view and search for the maximum number for field names like 'ConcatField_%'. When you have this information, you can add that many of following sql code fragment " CASE WHEN ConcatField_17 IS NULL AND ConcatField_16 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_17 is null and ConcatField_16 IS NULL then '' ELSE ' & ' + ConcatField_17 END + "
– Eralper
Jan 2 at 6:17
@MatthewHancock . . . A table has a fixed number of columns. I don't understand your data model, but you have a problem if the number of columns in a table can change.
– Gordon Linoff
Jan 2 at 12:46
add a comment |
You can build a dynamic SQL query maybe. By concatenating SQL commands as string pieces for a final statement then execute it with sp_executesql command as shown in sample kodyaz.com/articles/…
– Eralper
Jan 2 at 5:44
I am familiar with dynamic sql statements, however not sure exactly how to use them in this case. Could you give a quick example of how I could use it to write one for this problem?
– Matthew Hancock
Jan 2 at 6:14
You can query the SYS.TABLE_COLUMNS system view and search for the maximum number for field names like 'ConcatField_%'. When you have this information, you can add that many of following sql code fragment " CASE WHEN ConcatField_17 IS NULL AND ConcatField_16 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_17 is null and ConcatField_16 IS NULL then '' ELSE ' & ' + ConcatField_17 END + "
– Eralper
Jan 2 at 6:17
@MatthewHancock . . . A table has a fixed number of columns. I don't understand your data model, but you have a problem if the number of columns in a table can change.
– Gordon Linoff
Jan 2 at 12:46
You can build a dynamic SQL query maybe. By concatenating SQL commands as string pieces for a final statement then execute it with sp_executesql command as shown in sample kodyaz.com/articles/…
– Eralper
Jan 2 at 5:44
You can build a dynamic SQL query maybe. By concatenating SQL commands as string pieces for a final statement then execute it with sp_executesql command as shown in sample kodyaz.com/articles/…
– Eralper
Jan 2 at 5:44
I am familiar with dynamic sql statements, however not sure exactly how to use them in this case. Could you give a quick example of how I could use it to write one for this problem?
– Matthew Hancock
Jan 2 at 6:14
I am familiar with dynamic sql statements, however not sure exactly how to use them in this case. Could you give a quick example of how I could use it to write one for this problem?
– Matthew Hancock
Jan 2 at 6:14
You can query the SYS.TABLE_COLUMNS system view and search for the maximum number for field names like 'ConcatField_%'. When you have this information, you can add that many of following sql code fragment " CASE WHEN ConcatField_17 IS NULL AND ConcatField_16 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_17 is null and ConcatField_16 IS NULL then '' ELSE ' & ' + ConcatField_17 END + "
– Eralper
Jan 2 at 6:17
You can query the SYS.TABLE_COLUMNS system view and search for the maximum number for field names like 'ConcatField_%'. When you have this information, you can add that many of following sql code fragment " CASE WHEN ConcatField_17 IS NULL AND ConcatField_16 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_17 is null and ConcatField_16 IS NULL then '' ELSE ' & ' + ConcatField_17 END + "
– Eralper
Jan 2 at 6:17
@MatthewHancock . . . A table has a fixed number of columns. I don't understand your data model, but you have a problem if the number of columns in a table can change.
– Gordon Linoff
Jan 2 at 12:46
@MatthewHancock . . . A table has a fixed number of columns. I don't understand your data model, but you have a problem if the number of columns in a table can change.
– Gordon Linoff
Jan 2 at 12:46
add a comment |
3 Answers
3
active
oldest
votes
I think it's better you use CONCAT()
built-in SQL function than Case, because it can handle a null value. for example, you can use
SELECT
DISTINCT DENSE_RANK() OVER(
PARTITION BY propertyid
ORDER BY
plannumber
) AS rank,
propertyid,
CONCAT(
planprefix, plannumber, ConcatField_1,
ConcatField_2...
) INTO #RPD_STAGING
FROM
#concat_base
Thanks for the tip. I didn't know that Concat() would allow null values. Do you know of a way to deal with no set number of fields? I guess I could write 100 concat fields, but if possible would like a way so in the event 101 are required it dosent fail or miss any data.
– Matthew Hancock
Jan 2 at 5:41
CONCAT() is handling ISNULL() and COALESCE() solutions in this case. Concatenating string and numeric values with "+" expression can cause syntax errors. So the solution can be using one of these functions.
– Eralper
Jan 2 at 5:46
@MatthewHancock You would need to know the columns in the parsing phase when you write the SQL query. So you would concat(col1,col2,...,coln) present in the table concat_base
– George Joseph
Jan 2 at 7:22
add a comment |
This code should allow to concatenate over an unknown number of columns starting with "ConcatField_" I am unable to correct/debug the code(I am not running SQL server), but all of the elements are here to allow dynamic concatenation across an unknown number of fields assuming they all start with ConcatField.
SELECT
DISTINCT DENSE_RANK() OVER(
PARTITION BY propertyid
ORDER BY
plannumber
) AS rank, P,
propertyid,
) INTO #RPD_STAGING
FROM
#concat_base,
CROSS APPLY (SELECT CONCAT(', ' , col.Name)
FROM INFORMATION_SCHEMA.COLUMNS AS col
WHERE col like 'ConcatField*'
FOR XML PATH('') ) AS P (Concat_list)
add a comment |
Thanks for all the suggestions, I ended up rewriting the whole thing.
Instead of generating an indefinite number of columns I did something like the following!
DROP TABLE IF EXISTS #temp,#temp2
CREATE TABLE #temp
(
ID INT
,Parcel int
,lot NVARCHAR(255)
);
INSERT INTO #temp(ID, Parcel, lot)
VALUES
(1,111 ,1 ),(1,111 ,2 ),(1,111 ,3 ),(2,1212,1 ),(2,1212,3 ),(2,1212,4 ),(3,1333,1 ),(3,1333,7 ),(4,5555,1 ),(4,5555,7 )
,(4,5544,1 ),(4,5544,2 ),(5,1809,1 ),(5,1809,2 ),(5,1809,3 ),(5,1809,5 ),(5,1810,6 ),(5,1810,7 ),(5,1810,8 )
SELECT ID,Parcel,
(SELECT lot + ','
FROM #temp p2
WHERE p1.ID = p2.ID
AND p1.Parcel = p2.Parcel
ORDER BY ID,Parcel
FOR XML PATH ('')) AS Products
INTO #temp2 FROM #temp p1
GROUP BY p1.ID,p1.Parcel
SELECT * FROM #temp2
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54001112%2fconcatenation-of-columns-with-no-fixed-number-of-columns-in-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
I think it's better you use CONCAT()
built-in SQL function than Case, because it can handle a null value. for example, you can use
SELECT
DISTINCT DENSE_RANK() OVER(
PARTITION BY propertyid
ORDER BY
plannumber
) AS rank,
propertyid,
CONCAT(
planprefix, plannumber, ConcatField_1,
ConcatField_2...
) INTO #RPD_STAGING
FROM
#concat_base
Thanks for the tip. I didn't know that Concat() would allow null values. Do you know of a way to deal with no set number of fields? I guess I could write 100 concat fields, but if possible would like a way so in the event 101 are required it dosent fail or miss any data.
– Matthew Hancock
Jan 2 at 5:41
CONCAT() is handling ISNULL() and COALESCE() solutions in this case. Concatenating string and numeric values with "+" expression can cause syntax errors. So the solution can be using one of these functions.
– Eralper
Jan 2 at 5:46
@MatthewHancock You would need to know the columns in the parsing phase when you write the SQL query. So you would concat(col1,col2,...,coln) present in the table concat_base
– George Joseph
Jan 2 at 7:22
add a comment |
I think it's better you use CONCAT()
built-in SQL function than Case, because it can handle a null value. for example, you can use
SELECT
DISTINCT DENSE_RANK() OVER(
PARTITION BY propertyid
ORDER BY
plannumber
) AS rank,
propertyid,
CONCAT(
planprefix, plannumber, ConcatField_1,
ConcatField_2...
) INTO #RPD_STAGING
FROM
#concat_base
Thanks for the tip. I didn't know that Concat() would allow null values. Do you know of a way to deal with no set number of fields? I guess I could write 100 concat fields, but if possible would like a way so in the event 101 are required it dosent fail or miss any data.
– Matthew Hancock
Jan 2 at 5:41
CONCAT() is handling ISNULL() and COALESCE() solutions in this case. Concatenating string and numeric values with "+" expression can cause syntax errors. So the solution can be using one of these functions.
– Eralper
Jan 2 at 5:46
@MatthewHancock You would need to know the columns in the parsing phase when you write the SQL query. So you would concat(col1,col2,...,coln) present in the table concat_base
– George Joseph
Jan 2 at 7:22
add a comment |
I think it's better you use CONCAT()
built-in SQL function than Case, because it can handle a null value. for example, you can use
SELECT
DISTINCT DENSE_RANK() OVER(
PARTITION BY propertyid
ORDER BY
plannumber
) AS rank,
propertyid,
CONCAT(
planprefix, plannumber, ConcatField_1,
ConcatField_2...
) INTO #RPD_STAGING
FROM
#concat_base
I think it's better you use CONCAT()
built-in SQL function than Case, because it can handle a null value. for example, you can use
SELECT
DISTINCT DENSE_RANK() OVER(
PARTITION BY propertyid
ORDER BY
plannumber
) AS rank,
propertyid,
CONCAT(
planprefix, plannumber, ConcatField_1,
ConcatField_2...
) INTO #RPD_STAGING
FROM
#concat_base
edited Jan 2 at 6:36


Prashant Pimpale
3,5273933
3,5273933
answered Jan 2 at 5:32
ehsan_rakhshaniehsan_rakhshani
111
111
Thanks for the tip. I didn't know that Concat() would allow null values. Do you know of a way to deal with no set number of fields? I guess I could write 100 concat fields, but if possible would like a way so in the event 101 are required it dosent fail or miss any data.
– Matthew Hancock
Jan 2 at 5:41
CONCAT() is handling ISNULL() and COALESCE() solutions in this case. Concatenating string and numeric values with "+" expression can cause syntax errors. So the solution can be using one of these functions.
– Eralper
Jan 2 at 5:46
@MatthewHancock You would need to know the columns in the parsing phase when you write the SQL query. So you would concat(col1,col2,...,coln) present in the table concat_base
– George Joseph
Jan 2 at 7:22
add a comment |
Thanks for the tip. I didn't know that Concat() would allow null values. Do you know of a way to deal with no set number of fields? I guess I could write 100 concat fields, but if possible would like a way so in the event 101 are required it dosent fail or miss any data.
– Matthew Hancock
Jan 2 at 5:41
CONCAT() is handling ISNULL() and COALESCE() solutions in this case. Concatenating string and numeric values with "+" expression can cause syntax errors. So the solution can be using one of these functions.
– Eralper
Jan 2 at 5:46
@MatthewHancock You would need to know the columns in the parsing phase when you write the SQL query. So you would concat(col1,col2,...,coln) present in the table concat_base
– George Joseph
Jan 2 at 7:22
Thanks for the tip. I didn't know that Concat() would allow null values. Do you know of a way to deal with no set number of fields? I guess I could write 100 concat fields, but if possible would like a way so in the event 101 are required it dosent fail or miss any data.
– Matthew Hancock
Jan 2 at 5:41
Thanks for the tip. I didn't know that Concat() would allow null values. Do you know of a way to deal with no set number of fields? I guess I could write 100 concat fields, but if possible would like a way so in the event 101 are required it dosent fail or miss any data.
– Matthew Hancock
Jan 2 at 5:41
CONCAT() is handling ISNULL() and COALESCE() solutions in this case. Concatenating string and numeric values with "+" expression can cause syntax errors. So the solution can be using one of these functions.
– Eralper
Jan 2 at 5:46
CONCAT() is handling ISNULL() and COALESCE() solutions in this case. Concatenating string and numeric values with "+" expression can cause syntax errors. So the solution can be using one of these functions.
– Eralper
Jan 2 at 5:46
@MatthewHancock You would need to know the columns in the parsing phase when you write the SQL query. So you would concat(col1,col2,...,coln) present in the table concat_base
– George Joseph
Jan 2 at 7:22
@MatthewHancock You would need to know the columns in the parsing phase when you write the SQL query. So you would concat(col1,col2,...,coln) present in the table concat_base
– George Joseph
Jan 2 at 7:22
add a comment |
This code should allow to concatenate over an unknown number of columns starting with "ConcatField_" I am unable to correct/debug the code(I am not running SQL server), but all of the elements are here to allow dynamic concatenation across an unknown number of fields assuming they all start with ConcatField.
SELECT
DISTINCT DENSE_RANK() OVER(
PARTITION BY propertyid
ORDER BY
plannumber
) AS rank, P,
propertyid,
) INTO #RPD_STAGING
FROM
#concat_base,
CROSS APPLY (SELECT CONCAT(', ' , col.Name)
FROM INFORMATION_SCHEMA.COLUMNS AS col
WHERE col like 'ConcatField*'
FOR XML PATH('') ) AS P (Concat_list)
add a comment |
This code should allow to concatenate over an unknown number of columns starting with "ConcatField_" I am unable to correct/debug the code(I am not running SQL server), but all of the elements are here to allow dynamic concatenation across an unknown number of fields assuming they all start with ConcatField.
SELECT
DISTINCT DENSE_RANK() OVER(
PARTITION BY propertyid
ORDER BY
plannumber
) AS rank, P,
propertyid,
) INTO #RPD_STAGING
FROM
#concat_base,
CROSS APPLY (SELECT CONCAT(', ' , col.Name)
FROM INFORMATION_SCHEMA.COLUMNS AS col
WHERE col like 'ConcatField*'
FOR XML PATH('') ) AS P (Concat_list)
add a comment |
This code should allow to concatenate over an unknown number of columns starting with "ConcatField_" I am unable to correct/debug the code(I am not running SQL server), but all of the elements are here to allow dynamic concatenation across an unknown number of fields assuming they all start with ConcatField.
SELECT
DISTINCT DENSE_RANK() OVER(
PARTITION BY propertyid
ORDER BY
plannumber
) AS rank, P,
propertyid,
) INTO #RPD_STAGING
FROM
#concat_base,
CROSS APPLY (SELECT CONCAT(', ' , col.Name)
FROM INFORMATION_SCHEMA.COLUMNS AS col
WHERE col like 'ConcatField*'
FOR XML PATH('') ) AS P (Concat_list)
This code should allow to concatenate over an unknown number of columns starting with "ConcatField_" I am unable to correct/debug the code(I am not running SQL server), but all of the elements are here to allow dynamic concatenation across an unknown number of fields assuming they all start with ConcatField.
SELECT
DISTINCT DENSE_RANK() OVER(
PARTITION BY propertyid
ORDER BY
plannumber
) AS rank, P,
propertyid,
) INTO #RPD_STAGING
FROM
#concat_base,
CROSS APPLY (SELECT CONCAT(', ' , col.Name)
FROM INFORMATION_SCHEMA.COLUMNS AS col
WHERE col like 'ConcatField*'
FOR XML PATH('') ) AS P (Concat_list)
edited Jan 4 at 3:34
answered Jan 2 at 7:40
StromStrom
2,303322
2,303322
add a comment |
add a comment |
Thanks for all the suggestions, I ended up rewriting the whole thing.
Instead of generating an indefinite number of columns I did something like the following!
DROP TABLE IF EXISTS #temp,#temp2
CREATE TABLE #temp
(
ID INT
,Parcel int
,lot NVARCHAR(255)
);
INSERT INTO #temp(ID, Parcel, lot)
VALUES
(1,111 ,1 ),(1,111 ,2 ),(1,111 ,3 ),(2,1212,1 ),(2,1212,3 ),(2,1212,4 ),(3,1333,1 ),(3,1333,7 ),(4,5555,1 ),(4,5555,7 )
,(4,5544,1 ),(4,5544,2 ),(5,1809,1 ),(5,1809,2 ),(5,1809,3 ),(5,1809,5 ),(5,1810,6 ),(5,1810,7 ),(5,1810,8 )
SELECT ID,Parcel,
(SELECT lot + ','
FROM #temp p2
WHERE p1.ID = p2.ID
AND p1.Parcel = p2.Parcel
ORDER BY ID,Parcel
FOR XML PATH ('')) AS Products
INTO #temp2 FROM #temp p1
GROUP BY p1.ID,p1.Parcel
SELECT * FROM #temp2
add a comment |
Thanks for all the suggestions, I ended up rewriting the whole thing.
Instead of generating an indefinite number of columns I did something like the following!
DROP TABLE IF EXISTS #temp,#temp2
CREATE TABLE #temp
(
ID INT
,Parcel int
,lot NVARCHAR(255)
);
INSERT INTO #temp(ID, Parcel, lot)
VALUES
(1,111 ,1 ),(1,111 ,2 ),(1,111 ,3 ),(2,1212,1 ),(2,1212,3 ),(2,1212,4 ),(3,1333,1 ),(3,1333,7 ),(4,5555,1 ),(4,5555,7 )
,(4,5544,1 ),(4,5544,2 ),(5,1809,1 ),(5,1809,2 ),(5,1809,3 ),(5,1809,5 ),(5,1810,6 ),(5,1810,7 ),(5,1810,8 )
SELECT ID,Parcel,
(SELECT lot + ','
FROM #temp p2
WHERE p1.ID = p2.ID
AND p1.Parcel = p2.Parcel
ORDER BY ID,Parcel
FOR XML PATH ('')) AS Products
INTO #temp2 FROM #temp p1
GROUP BY p1.ID,p1.Parcel
SELECT * FROM #temp2
add a comment |
Thanks for all the suggestions, I ended up rewriting the whole thing.
Instead of generating an indefinite number of columns I did something like the following!
DROP TABLE IF EXISTS #temp,#temp2
CREATE TABLE #temp
(
ID INT
,Parcel int
,lot NVARCHAR(255)
);
INSERT INTO #temp(ID, Parcel, lot)
VALUES
(1,111 ,1 ),(1,111 ,2 ),(1,111 ,3 ),(2,1212,1 ),(2,1212,3 ),(2,1212,4 ),(3,1333,1 ),(3,1333,7 ),(4,5555,1 ),(4,5555,7 )
,(4,5544,1 ),(4,5544,2 ),(5,1809,1 ),(5,1809,2 ),(5,1809,3 ),(5,1809,5 ),(5,1810,6 ),(5,1810,7 ),(5,1810,8 )
SELECT ID,Parcel,
(SELECT lot + ','
FROM #temp p2
WHERE p1.ID = p2.ID
AND p1.Parcel = p2.Parcel
ORDER BY ID,Parcel
FOR XML PATH ('')) AS Products
INTO #temp2 FROM #temp p1
GROUP BY p1.ID,p1.Parcel
SELECT * FROM #temp2
Thanks for all the suggestions, I ended up rewriting the whole thing.
Instead of generating an indefinite number of columns I did something like the following!
DROP TABLE IF EXISTS #temp,#temp2
CREATE TABLE #temp
(
ID INT
,Parcel int
,lot NVARCHAR(255)
);
INSERT INTO #temp(ID, Parcel, lot)
VALUES
(1,111 ,1 ),(1,111 ,2 ),(1,111 ,3 ),(2,1212,1 ),(2,1212,3 ),(2,1212,4 ),(3,1333,1 ),(3,1333,7 ),(4,5555,1 ),(4,5555,7 )
,(4,5544,1 ),(4,5544,2 ),(5,1809,1 ),(5,1809,2 ),(5,1809,3 ),(5,1809,5 ),(5,1810,6 ),(5,1810,7 ),(5,1810,8 )
SELECT ID,Parcel,
(SELECT lot + ','
FROM #temp p2
WHERE p1.ID = p2.ID
AND p1.Parcel = p2.Parcel
ORDER BY ID,Parcel
FOR XML PATH ('')) AS Products
INTO #temp2 FROM #temp p1
GROUP BY p1.ID,p1.Parcel
SELECT * FROM #temp2
answered Jan 9 at 0:55
Matthew HancockMatthew Hancock
12
12
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54001112%2fconcatenation-of-columns-with-no-fixed-number-of-columns-in-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
KbSNrrSqLCbHMAqTHb0F
You can build a dynamic SQL query maybe. By concatenating SQL commands as string pieces for a final statement then execute it with sp_executesql command as shown in sample kodyaz.com/articles/…
– Eralper
Jan 2 at 5:44
I am familiar with dynamic sql statements, however not sure exactly how to use them in this case. Could you give a quick example of how I could use it to write one for this problem?
– Matthew Hancock
Jan 2 at 6:14
You can query the SYS.TABLE_COLUMNS system view and search for the maximum number for field names like 'ConcatField_%'. When you have this information, you can add that many of following sql code fragment " CASE WHEN ConcatField_17 IS NULL AND ConcatField_16 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_17 is null and ConcatField_16 IS NULL then '' ELSE ' & ' + ConcatField_17 END + "
– Eralper
Jan 2 at 6:17
@MatthewHancock . . . A table has a fixed number of columns. I don't understand your data model, but you have a problem if the number of columns in a table can change.
– Gordon Linoff
Jan 2 at 12:46