CODEWITHSUNDEEP
sql-server-2008
sganesh
sganesh

Reputation: 79

Convert String to comma separated values

I have a table as below 

DECLARE @T TABLE(Data VARCHAR(MAX))
INSERT INTO @T
SELECT 'SQL' UNION ALL SELECT 'JOB'

need output as below but without using any UDF.

Data  String
------------
SQL   S,Q,L
JOB   J,O,B

Please help me on this

Upvotes: 0

Views: 1178

Answers (4)

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

Late to the party, but here's a slightly shorter version:

DECLARE @T TABLE(Data VARCHAR(MAX));

INSERT INTO @T VALUES('SQL'),('JOB'),('FLOOB');

;WITH n AS (SELECT TOP (SELECT MAX(LEN(Data)) FROM @T) 
  n = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_objects
),
t AS (SELECT n, Data, Letter = SUBSTRING(t.Data, n.n, 1) FROM n
  INNER JOIN @T AS t ON SUBSTRING(t.Data, n.n, 1) > ''
)
SELECT Data, STUFF((SELECT ',' + letter FROM t AS t2
  WHERE t2.Data = t.Data ORDER BY t2.n FOR XML PATH(''), 
  TYPE).value(N'./text()[1]', N'varchar(max)'), 1, 1, '')
FROM t GROUP BY Data;

Results:

FLOOB   F,L,O,O,B
JOB     J,O,B
SQL     S,Q,L

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

Using the same approach I used for Initcap function here http://beyondrelational.com/modules/2/blogs/70/posts/10901/tsql-initcap-function-convert-a-string-to-proper-case.aspx

DECLARE @T TABLE(Data VARCHAR(MAX)) 
INSERT INTO @T 
SELECT 'SQL' UNION ALL SELECT 'JOB' 

select data,
        upper(replace(replace(replace(replace(replace(replace(replace(
        replace(replace(replace(replace(replace(replace(replace(
        replace(replace(replace(replace(replace(replace(replace(
        replace(replace(replace(replace(replace(
            ' '+data ,
        ' a','a,'),' b','b,'),'c','c,'),'d','d,'),'e','e,'),'f','f,'),
        ' g','g,'),' h','h,'),'i','i,'),'j','j,'),'k','k,'),'l','l,'),
        ' m','m,'),' n','n,'),'o','o,'),'p','p,'),'q','q,'),'r','r,'),
        ' s','s,'),' t','t,'),'u','u,'),'v','v,'),'w','w,'),'x','x,'),
        ' y','y,'),' z','z,')) as splitted_data
from
    @t

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24116

It is very easy to do with UDF. But If you want with out UDF, the only one way I can think of is

something like this

DECLARE @T TABLE(Data VARCHAR(MAX))
INSERT INTO @T
SELECT 'SQL' UNION ALL SELECT 'JOB'

select replace(replace(replace(data,'S','S,'),'Q','Q,'),'L','L,') from @T

here you have to replace all the 26 characters with replace function. ie, 'A' with 'A,' 'B' with 'B,' .... 'Z' with 'Z,'

Upvotes: 0

Jānis
Jānis

Reputation: 2266

Sure you can :). You can make it shorter too... 

DECLARE @T TABLE(Data VARCHAR(MAX))
INSERT INTO @T
SELECT 'SQL' UNION ALL SELECT 'JOB';    

With cte as
(
    Select Data, Len(Data) DataLength, 1 level
    From @t
    Union All 
    Select Data, DataLength - 1, level + 1
    From cte
    Where DataLength > 1 
),
cte2 as
(
    Select Data, SUBSTRING(Data, DataLength, 1) DataLetter, level
    From cte
),
cte3 as
(
    Select Data, 
        (
            SELECT DataLetter + ','
            FROM cte2 c
            Where c.Data = cte2.Data
            Order By level desc
            FOR XML PATH(''), TYPE

        ).value('.[1]', 'NVARCHAR(1000)') DataComa
    From cte2
    Group By Data
)
Select Data, substring(DataComa, 1, Len(DataComa) - 1) Data2
From cte3

Upvotes: 2

Related Questions