how to write SQL query for this result?

Question

I have so many long database so I used seq_no in commas separate using more than one sequence store in single column but now I want all sequence in a single column so I am confused how to create this sql result for this.

For example:

TABLE STRUCTURE 
SR_NO IS INT ,
SEQ_NO IS VARCHAR(MAX)

SR_NO   SEQ_NO
---------------------------------    
  1     1839073,
  2     1850097,1850098,
  3     1850099,1850100,1850110

I need to get this result:

SEQ_NO 
--------------
1839073
1850097
1850098
1850099
1850100
1850110

Thanks!

And if you want Sr_No column, do you want to keep the value or re-number? — jarlh, Apr 21 '15 at 11:49
So you are just doing a SELECT - and not cleaning up that old, comma separate values mess? — jarlh, Apr 21 '15 at 11:50
One word of advice: ***DON'T DO THIS!*** Don't store multiple values into a single database cell - this **violates** even the most basic **first normal form** of database design - and it ***WILL*** cause you grief over and over and over again. **Don't do it - seriously!** — marc_s, Apr 21 '15 at 11:52

score 5 · Accepted Answer · answered Apr 21 '15 at 12:08

5

declare @t table(Id int,seq varchar(100)) 
insert into @t (Id,seq) values (1,'1839073,'),(2,'1839073,1850098,'),(3,'1850099,1850100,1850110 ')



;With Cte as (
SELECT A.Id,  
     Split.a.value('.', 'VARCHAR(100)') AS Seq  
 FROM  
 (
     SELECT Id,  
         CAST ('<M>' + REPLACE(seq, ',', '</M><M>') + '</M>' AS XML) AS Data  
     FROM  @t
 ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a) )

 Select ID,Seq from Cte Where Seq > ''

answered Apr 21 '15 at 12:08

mohan111

8,633
4
28
55

Thanks mohan i solve my problem by giving this logic. thanks again. – Rajuu Parmar Apr 21 '15 at 12:19

score 3 · Answer 2 · answered Apr 21 '15 at 11:54

Try splitting it with XML

SELECT SR_NO, t.c.value('.', 'VARCHAR(2000)') COL1
FROM (
  SELECT SR_NO, x = CAST('<t>' + 
        REPLACE(SEQ_NO, ',', '</t><t>') + '</t>' AS XML) 
        FROM 
       (values(1,'1839073'),(2, '1850097,1850098'),
        (3, '1850099,1850100,1850110')) y(SR_NO, SEQ_NO)

) a
CROSS APPLY x.nodes('/t') t(c)

Result:

SR_NO  COL1
1      1839073
2      1850097
2      1850098
3      1850099
3      1850100
3      1850110

You can replace this with your table:

 (values (1,'1839073'),(2, '1850097,1850098'),
  (3, '1850099,1850100,1850110')) y(SR_NO, SEQ_NO)

score 2 · Answer 3 · edited May 23 '17 at 11:51

This should do it: (Replace YourTableName with your table name)

;WITH CTE(NEW_SEQ_NO, SEQ_NO) as (
SELECT LEFT(SEQ_NO, CHARINDEX(',',SEQ_NO + ',') -1),
    STUFF(SEQ_NO, 1, CHARINDEX(',',SEQ_NO + ','), '')
FROM YourTableName
WHERE SEQ_NO <> '' AND SEQ_NO IS NOT NULL 
UNION all
SELECT LEFT(SEQ_NO, CHARINDEX(',',SEQ_NO + ',') -1),
    STUFF(SEQ_NO, 1, CHARINDEX(',',SEQ_NO + ','), '')
FROM CTE
WHERE SEQ_NO <> '' AND SEQ_NO IS NOT NULL 
)

SELECT NEW_SEQ_NO from CTE ORDER BY NEW_SEQ_NO

You can check this topic for more information: Turning a Comma Separated string into individual rows

score 1 · Answer 4 · edited May 23 '17 at 12:14

I have written the following query after referring Turning a Comma Separated string into individual rows

It will work for you

create table STRUCTURE(SR_NO int, SEQ_NO varchar(max))
insert STRUCTURE select 1, '1839073,'
insert STRUCTURE select 2, '1850097,1850098,'
insert STRUCTURE select 3, '1850099,1850100,1850110'


;with tmp(SR_NO, DataItem, SEQ_NO) as (
select SR_NO, LEFT(SEQ_NO, CHARINDEX(',',SEQ_NO+',')-1),
STUFF(SEQ_NO, 1, CHARINDEX(',',SEQ_NO+','), '')
from STRUCTURE
union all
select SR_NO, LEFT(SEQ_NO, CHARINDEX(',',SEQ_NO+',')-1),
STUFF(SEQ_NO, 1, CHARINDEX(',',SEQ_NO+','), '')
from tmp
where SEQ_NO > ''
)

Select DataItem as  SEQ_NO from tmp order by SEQ_NO;

how to write SQL query for this result?

4 Answers4

Linked