CODEWITHSUNDEEP
sqlsql-server
asmgx
asmgx

Reputation: 7994

Split space separated values

I have many records like this

ID   Tag   Arr
1    A     87 34 92
2    A     34 35 38
3    A     39 88 92
4    B     24 49 39
5    B     38 88 23
6    C     39 37 99

I want the end result to look like this in a fast way

Tag Arr No
A   87  1
A   34  2
A   92  2
A   35  1
A   38  1
A   39  1
A   88  1
B   24  1
B   49  1
B   39  1
B   38  1
B   88  1
B   23  1
C   39  1
C   37  1
C   99  1

This is the query i built so far

SELECT DISTINCT T2.tag, 
    SUBSTRING(
        (
            SELECT ','+T1.Arr  AS [text()]
            FROM Tags T1
            WHERE T1.tag = T2.tag
            and filename = 1
            and tag not in ('U')
            ORDER BY T1.tag
            FOR XML PATH ('')
        ), 2, 1000) [Ts]
FROM Tags T2
WHERE  filename = 1
and tag not in ('U')

Upvotes: 1

Views: 223

Answers (2)

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try this using STRING_SPLIT (Transact-SQL) which works for SQL Server 2016 and later.

create table tblSampleValue(Id int, Tag Varchar(10), Arr Varchar(50))
insert into tblSampleValue Values
(1, 'A', '87 34 92'),
(2, 'A', '34 35 38'),
(3, 'A', '39 88 92'),
(4, 'B', '24 49 39'),
(5, 'B', '38 88 23'),
(6, 'C', '39 37 99')

Select * from tblSampleValue

select id
 , Tag
 , [value] as Arr 
from tblSampleValue 
cross apply string_split(Arr,' ')

Live db<>fiddle demo.

Upvotes: 4

D-Shih
D-Shih

Reputation: 46219

First, you need to use Split function to split Arr column with space.

CREATE FUNCTION udf_Split 
( @Words nvarchar(MAX)
, @splitStr varchar(50) 
)
RETURNS @Result_Table TABLE
       (
         [word] nvarchar(max) NULL
       )
BEGIN 
    Declare @TempStr nvarchar(MAX)

    WHILE (CHARINDEX(@splitStr,@Words)>0)
    BEGIN
        Set @TempStr=SUBSTRING(@Words,1,CHARINDEX(@splitStr,@Words)-1)
        Insert into @Result_Table (word) Values (@TempStr)

        Set @Words = REPLACE(@Words,@TempStr+@splitStr,'')
    END/*End While*/

    IF(LEN(RTRIM(LTRIM(@Words)))>0 And CHARINDEX(@splitStr,RTRIM(LTRIM(@Words)))=0) 
    Begin
        Set @TempStr=@Words 

        Insert into @Result_Table (word) Values (@TempStr)

    End /*End IF*/

   RETURN 
END

then use CROSS APPLY with Arr column

SELECT Tag,
       word,
       count(*) [no]
FROM Tags CROSS APPLY udf_Split(Arr,' ') v
GROUP BY Tag,
       word
ORDER BY Tag

sqlfiddle

Note:

if your SQL server version higher than 2016 there is an official function you can use STRING_SPLIT

Upvotes: 4

Related Questions