SQL Query

[vlitim]

I have a quey that return the following 5 rows

59
64
3,63,68,69
64
63,3

What I want to be able to do is a distinct insert into a table so the records inserted would be

3
59
63
64
68
69

Can someone tell me what the best way of doing this would be?

Cheers
Tim

 

[iker3000]

Do you have a minimal and maximal size for numbers?
Ho big is your table (not very big).
Are they only int-s in your table?

If so, I now a not very dificult but easy way:

You create a table with one column containing numbers form max value to min value (for example 1 ... 100) - Numbers

Lets say your query is called Number_strings

SELECT DISTINCT
Numbers.Column
FROM
Numbers
INNER JOIN Number_strings
ON ',' + Number_strings.Column + ',' LIKER '%,' + CAST
(Numbers.Column AS nvarchar) + ',%'

The Join will work as the following:
For Numbers.Column = 3 AND Number_strings.Column =
'3,63,68,69'

ON ',3,63,68,69,' LIKER '%,3,%'

You should get what you want, but I haven't tested it, because I've no SQL-Server at hand.
It is not a very elegant way, and not very efficient, but it is easy.

Iker