-3

If I issue 

SELECT ID FROM TestAhmet

I get this result:

1,2,3,4,5

but what I really need is multiple column with all the values separated by comma, like this:

1
3
5
2
4

How do I do this?

GMB
  • 216,147
  • 25
  • 84
  • 135
Subash
  • 3
  • 3
  • 4
    If possible I would recommend changing the Table to not store comma separated values in the first place. Instead have those values in a separate table with a many-to-one relationship to the TestAhmet table. – juharr Jun 10 '20 at 13:34
  • Do you want to achieve that on server side or client side? – Guru Stron Jun 10 '20 at 13:38
  • Hi Guru, Server side. – Subash Jun 10 '20 at 13:42
  • 1
    @Subash don't store such values in the first place. It's a bug. In all SQL Server versions in mainstream support (2016+) you can use `STRING_SPLIT`. You'll find a *lot* of similar SO questions too. The XML Path technique is probably the fastest – Panagiotis Kanavos Jun 10 '20 at 13:44
  • The best option for any version would be to split the data on the server and store it in a separate table, on multiple rows, just like any other data. Another option would be to store the data as XML and use XML methods for querying. – Panagiotis Kanavos Jun 10 '20 at 13:45
  • Hi Guru, Thnaks for your suggestion:-) – Subash Jun 10 '20 at 13:47
  • @Subash does the table really contain `1,2,3,4,5` as a single ID value? In that case you have a far more serious problem and won't be able to query your data. Was there a display problem perhaps, that made multiple values appear in a single row instead of multiple rows perhaps? – Panagiotis Kanavos Jun 10 '20 at 13:48
  • Hi Guru, Thats not a id value, i have posted just for eg. – Subash Jun 10 '20 at 13:49
  • @Guru actually you assume this as logic not assume this as table structure. – Subash Jun 10 '20 at 13:50
  • You want the output as multiple rows or columns? – AJITH Jun 10 '20 at 14:32
  • I have added an answer that works on SQL Server 2012, but do note the the data should be stored in a separate table, your `ID` column is not normalized – Cleptus Jun 10 '20 at 14:58

3 Answers3

3

If you are running SQL Server 2016 or later, you can use table-valued function string_split():

select value
from TestAhmet t
cross apply string_split(t.id, ',')
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Hi GMB, Thanks for your update, bad luck i am using sql 2012. Please give solution for that version. – Subash Jun 10 '20 at 13:36
  • 1
    @Subash SO is not a free code-writing service. Splitting strings has been (and likely will continue to be) a constant topic; a little searching will find many examples of code that will accomplish this for any version of sql server. – SMor Jun 10 '20 at 13:49
  • @Subash I have edited your question to add sql 2012 tag, so it gets more accurate answers – Cleptus Jun 10 '20 at 14:10
  • @Subash In 2012 you can use a replace to form XML nodes and the XML related functions – Cleptus Jun 10 '20 at 14:14
0

You can make this query as a function and use it across procs.

DECLARE @list varchar(MAX)= '1,2,3,4,5',
        @pos        int,
        @nextpos    int,
        @valuelen   int
DECLARE  @tbl TABLE (number int  NULL)

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      INSERT @tbl (number)
         VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
      SELECT @pos = @nextpos
   END

   SELECT * FROM @tbl
unni_ukmv
  • 51
  • 3
0

You can use XML related functions that are available on SQL Server 2012

Reference: XML nodes() function and XML value() function

DECLARE @text_to_split nvarchar(100) = '1,10,4,6'

SELECT document.node.value('.', 'int') as id
FROM
     (SELECT CAST('<content><x>' + REPLACE(@text_to_split, ',', '</x><x>') + '</x></content>' AS XML) as content) xml_data
     CROSS APPLY xml_data.content.nodes('/content/x') as document(node)

The output is:

1
10
4
6

Explanation

What we do is translate that data into a valid XML: 
CAST('<content><x>' + REPLACE(@text_to_split, ',', '</x><x>') + '</x></content>' AS XML) as content

Then we do a CROSS APPLY to be able to work with it as a table estructured XML data

CROSS APPLY xml_data.content.nodes('/content/x') as document(node)

And finally we extract the content of the XML nodes and cast it as integers

SELECT document.node.value('.', 'int') as id
Cleptus
  • 3,446
  • 4
  • 28
  • 34