Concatenation of columns with no fixed number of columns in SQL

Multi tool use
Multi tool use












0















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.










share|improve this question

























  • 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
















0















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.










share|improve this question

























  • 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














0












0








0








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.










share|improve this question
















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-server






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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



















  • 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












3 Answers
3






active

oldest

votes


















0














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





share|improve this answer


























  • 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



















0














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)





share|improve this answer

































    0














    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





    share|improve this answer























      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
      });


      }
      });














      draft saved

      draft discarded


















      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









      0














      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





      share|improve this answer


























      • 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
















      0














      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





      share|improve this answer


























      • 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














      0












      0








      0







      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





      share|improve this answer















      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






      share|improve this answer














      share|improve this answer



      share|improve this answer








      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



















      • 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













      0














      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)





      share|improve this answer






























        0














        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)





        share|improve this answer




























          0












          0








          0







          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)





          share|improve this answer















          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)






          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Jan 4 at 3:34

























          answered Jan 2 at 7:40









          StromStrom

          2,303322




          2,303322























              0














              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





              share|improve this answer




























                0














                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





                share|improve this answer


























                  0












                  0








                  0







                  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





                  share|improve this answer













                  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






                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Jan 9 at 0:55









                  Matthew HancockMatthew Hancock

                  12




                  12






























                      draft saved

                      draft discarded




















































                      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.




                      draft saved


                      draft discarded














                      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





















































                      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
                      8IlRJ,9l4V6hoTqm rJHi gj,yQX51bVezwLkxFLVqHV2sNNkTdak1L3zRbK i9AoxNjZ,9bvr H,o N9aFAKzm2G2ODt,4

                      Popular posts from this blog

                      Monofisismo

                      Angular Downloading a file using contenturl with Basic Authentication

                      Olmecas