question

GopinathDesai-8233 avatar image
0 Votes"
GopinathDesai-8233 asked EchoLiu-msft commented

Comma seperated values to rows

Hi All,
can please help to write sql where i have convert comma separated values to rows. Below is the result am looking for :

declare @Tab table (ID int,CommaValues varchar(100),businesskey varchar(100))
insert into @Tab
values (1,'21520203517013900,21520210047026800,21520210047025100','XYZ'),
(2,'21520203517013900,21520210047026800,21520210047025100','XYZ'),
(3,'21520203517013900,21520210047026800,21520210047025100','XYZ'),
(4,'21520210047025101,21520210047027901','ABC'),
(5,'21520210047025101,21520210047027901','ABC')
select * from @Tab

Result as below

ID CommaValues businesskey SeperatedValues
1 21520203517013900,21520210047026800,21520210047025100 XYZ 21520203517013900
2 21520203517013900,21520210047026800,21520210047025100 XYZ 21520210047026800
3 21520203517013900,21520210047026800,21520210047025100 XYZ 21520210047025100
4 21520210047025101,21520210047027901 ABC 21520210047025101
5 21520210047025101,21520210047027901 ABC 21520210047027901

sql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·

Sure @YitzhakKhabinsky-0887 will do from next time.

0 Votes 0 ·

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 commented

Hi @GopinathDesai-8233,

Please try the following solution.

SQL

 -- DDL and sample data population, start
 DECLARE @tbl TABLE (ID int,CommaValues varchar(100),businesskey varchar(100));
 INSERT INTO @tbl VALUES 
 (1,'21520203517013900,21520210047026800,21520210047025100','XYZ'),
 (2,'21520203517013900,21520210047026800,21520210047025100','XYZ'),
 (3,'21520203517013900,21520210047026800,21520210047025100','XYZ'),
 (4,'21520210047025101,21520210047027901','ABC'),
 (5,'21520210047025101,21520210047027901','ABC');
 -- DDL and sample data population, end
    
 DECLARE @separator CHAR(1) = ',';
    
 ;WITH rs AS
 (
  SELECT *
  , ROW_NUMBER() OVER (PARTITION BY businesskey ORDER BY ID) AS seq
  FROM @tbl
  CROSS APPLY (VALUES (TRY_CAST('<root><r>' + 
    REPLACE(CommaValues, @separator, '</r><r>') + 
    '</r></root>' AS XML))) AS t(c)
 )
 SELECT ID, CommaValues, businesskey
  , c.value('(/root/r[sql:column("seq")]/text())[1]','VARCHAR(30)') AS Result
 FROM rs
 ORDER BY ID;

Output

 +----+-------------------------------------------------------+------+-------------------+
 | ID |                      CommaValues                      | bkey |      Result       |
 +----+-------------------------------------------------------+------+-------------------+
 |  1 | 21520203517013900,21520210047026800,21520210047025100 | XYZ  | 21520203517013900 |
 |  2 | 21520203517013900,21520210047026800,21520210047025100 | XYZ  | 21520210047026800 |
 |  3 | 21520203517013900,21520210047026800,21520210047025100 | XYZ  | 21520210047025100 |
 |  4 |                   21520210047025101,21520210047027901 | ABC  | 21520210047025101 |
 |  5 |                   21520210047025101,21520210047027901 | ABC  | 21520210047027901 |
 +----+-------------------------------------------------------+------+-------------------+


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@GopinathDesai-8233,

Glad to hear that the proposed solution is working for you.
Please don't forget to mark is as Answer.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

For SQL 2016 and later:

declare @Tab table (ID int,CommaValues varchar(100),businesskey varchar(100))
insert into @Tab
values (1,'21520203517013900,21520210047026800,21520210047025100','XYZ'),
(2,'21520203517013900,21520210047026800,21520210047025100','XYZ'),
(3,'21520203517013900,21520210047026800,21520210047025100','XYZ'),
(4,'21520210047025101,21520210047027901','ABC'),
(5,'21520210047025101,21520210047027901','ABC')
select * from @Tab
SELECT T.ID, T.CommaValues, T.businesskey, s.value AS SeparatedValues
FROM   @Tab T
CROSS  APPLY string_split(T.CommaValues, ',') AS s
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ErlandSommarskog,

The query is producing 13 rows. The OP desired output has 5 rows.

0 Votes 0 ·

I noticed that, but it was not clear why Gopinath only wanted five rows. After all, the title says "Comma-separated values to rows", so it seemed reasonable that Gopinath wanted all rows. Unfortunately, posters are not always accurate with their desired result.

1 Vote 1 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @GopinathDesai-8233,

Please also check:

 CREATE TABLE #Tab(ID int,CommaValues varchar(100),businesskey varchar(100))
 INSERT INTO #Tab
 values (1,'21520203517013900,21520210047026800,21520210047025100','XYZ'),
 (2,'21520203517013900,21520210047026800,21520210047025100','XYZ'),
 (3,'21520203517013900,21520210047026800,21520210047025100','XYZ'),
 (4,'21520210047025101,21520210047027901','ABC'),
 (5,'21520210047025101,21520210047027901','ABC')
    
 --1.Use string_split(applies to SQL Server 2016 and later)
 ;WITH cte
 as(SELECT DISTINCT v.CommaValues,v.businesskey, s.value AS SeparatedValues
 FROM #Tab v
 CROSS  APPLY string_split(v.CommaValues, ',') AS s)
    
 SELECT ROW_NUMBER() OVER(ORDER BY CommaValues,businesskey) ID,* 
 FROM cte c
    
 --2.Create user-defined functions(applies to SQL Server all supported versions) 
 CREATE FUNCTION SplitStr(@Sourcestr VARCHAR(8000), @Seprate VARCHAR(100))     
 RETURNS @result TABLE(F1 VARCHAR(100))     
   AS       
    BEGIN     
    DECLARE @sql AS VARCHAR(100)     
   SET @Sourcestr=@Sourcestr+@Seprate       
   WHILE(@Sourcestr<>'')     
   BEGIN     
     SET @sql=left(@Sourcestr,CHARINDEX(',',@Sourcestr,1)-1)     
     INSERT @result VALUES(@sql)     
      SET @Sourcestr=STUFF(@Sourcestr,1,CHARINDEX(',',@Sourcestr,1),'')     
    END     
    RETURN  
    END
 GO
    
 ;WITH cte
 as(SELECT DISTINCT v.CommaValues,v.businesskey, s.F1 AS SeparatedValues
 FROM #Tab v
 CROSS APPLY SplitStr(v.CommaValues,',') as s)
    
 SELECT ROW_NUMBER() OVER(ORDER BY CommaValues,businesskey) ID,* 
 FROM cte c
    
 DROP FUNCTION SplitStr

Output:
128552-image.png


If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.




image.png (12.7 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @EchoLiu-msft ,
Thanks for reply.
Forgot to put sql server version am using ....as mentioned by @YitzhakKhabinsky-0887

0 Votes 0 ·

You're welcome. The answer I provided contains methods suitable for all SQL Server versions, and you can choose the appropriate method according to your actual situation.

Of course, when you post a question next time, it would be great if you can provide the SQL Server version.

0 Votes 0 ·