0

I use 

SELECT Distinct  [Material]  FROM Mytable

and get the following result:

Material
 --------------
 1469CDHKabef
 237BI
 237BI5

Is there way to filter this output further so that each letter that appears in the individual strings (row) only appears once?

Desired output is:

12345679BCDHKIabef

or even better, get the whole thing choped upp so that each letter/number ends up in a row of its own

Sai Kalyan Kumar Akshinthala
  • 11,704
  • 8
  • 43
  • 67
Lumpi
  • 2,697
  • 5
  • 38
  • 47
  • Dint get your actual requirement in the question you have given. How you have ordered the "Desired output" while the result is of different order. – Sai Kalyan Kumar Akshinthala Nov 08 '11 at 10:13
  • He wants to join the result in a single row and that to in sorted/ordered manner. – Rahul Nov 08 '11 at 10:15
  • The order of the output does not matter to me, as long as each number/character only apears once in the output.In the original output I have 237BI twice. I only need each letter/number once. – Lumpi Nov 08 '11 at 10:17

1 Answers1

1

The below uses master..spt_values as an adhoc auxiliary numbers table just for demonstration purposes. You should create your own.

WITH Numbers(N) AS
(
SELECT number
FROM master..spt_values
WHERE type='P' AND number > 0
), Materials(Material) As
(
SELECT '1469CDHKabef' UNION ALL
SELECT '237BI' UNION ALL
SELECT '237BI5'
)
SELECT DISTINCT SUBSTRING(Material,N,1)
FROM Materials
JOIN Numbers ON N <= LEN(Material)
Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Ok, thanks, I guess I have some SQL studying to do ;-) Never heard of master..spt_values... – Lumpi Nov 08 '11 at 10:31
  • There's no particular reason you should have heard of it. It's just a system table that happens to be useful for this as it contains a sequential set of numbers from `0` to `2047` and is guaranteed to be present. – Martin Smith Nov 08 '11 at 10:36