0

I have a table that looks like so:

Id   SubNumber Values
1    1         1;4;8;3
2    2         8;9;7;10
3    3         41;45;23;0

I will not always only have 4 values and the number of "SubNumbers" can be greater than 3. Is there any way I can query this table to look like this?

Id    SubNumber    1   2   3  4

1        1         1   4   8  3
2        2         8   9   7  10
2        3        41  45  23  0

The rows will always have the same number of values delimited by a semicolon but the amount separated by a semicolon can vary. So a table may even have 10 values or 1 or more.
The 2nd table doesn't have to have numbers to represent the values. It can even be blank or the default that is given by SQL when no name is provided.

This is not a duplicate of the example provided because this deals with separating a dynamic number of values into columns.

Himanshu
  • 4,327
  • 16
  • 31
  • 39
user3654442
  • 179
  • 1
  • 4
  • 19
  • The best option would be to stop violating 1NF by storing multiple values in a tuple like this. If you are not yet on 2016 then I would advise you to look at this article. http://sqlperformance.com/2012/07/t-sql-queries/split-strings The answer in the possible duplicate response with the most votes is the absolute worst way to split strings. Looping in this situation is a bad way to do it. – Sean Lange Dec 22 '16 at 17:36
  • Take a peek at http://stackoverflow.com/questions/40922486/t-sql-separate-string-into-multiple-columns/40922532#40922532 You will have to change ...Replace(A.Column1,' '... with Replace(A.Values,';' – John Cappelletti Dec 22 '16 at 18:05
  • For performance considerations, consider adapting CLR to your needs: https://sqlperformance.com/2012/07/t-sql-queries/split-strings – Jaaz Cole Dec 22 '16 at 18:16

0 Answers0