sql query,loop

Question

Hi, I have got another query ..

    IF OBJECT_ID('dbo.test', 'U') IS NOT NULL   
      DROP TABLE dbo.test;   
      
    create table test (clmn int , modalities nvarchar(175) )  
    go  
      
    insert into test values (1,'General Dental|General Dental|General Dental|General Dental|General Dental|General Dental|General Dental|General Dental|General Dental|General Dental|General Dental')  
    insert into test values (2,'Chiropractic|Chiropractic|Chiropractic|Chiropractic|Chiropractic')  
       
    go  
      
    select * from dbo.test;  
    go  
      
      
      
    now the i/p can have three rows, four rows etc.. so this table can keep changing  

desired o/p

Blockquote

Answer 1

The guessing game continue... Maybe this is what you want?

SELECT t.clmn,v.ordinal ,v.value as MODALITY  
FROM dbo.test t   
CROSS APPLY STRING_SPLIT(t.modalities,'|',1) v  
GO  

This fits your requested result

Note, I only got this idea after I saw @EchoLiu-MSFT guess that you need t split the content. Using the third parameter can solve the needs in this case (it is a optional parameter which is not supported in current versions of SQL Server on premises)

Answer 2

Hi @rdboy_9727 ,

Sorry, your question is not clear enough for me. Maybe this is what you want:

SELECT t.clmn,v.value as MODALITY  
FROM dbo.test t   
CROSS APPLY STRING_SPLIT(t.modalities,'|') v  

Output:

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

Please try:

SELECT ROW_NUMBER() OVER(PARTITION BY t.clmn ORDER BY t.clmn) i,  
v.value as MODALITY  
FROM dbo.test t   
CROSS APPLY STRING_SPLIT(t.modalities,'|') v  

Output:

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 4

Hi all, sorry for not being clear on my query . i'll be more specific going ahead .

@EchoLiu-MSFT , you have 99% given what I want . Incase I need another column in my results showing the count i.e 1,2,3,4,5,6,7,8,9,10,11 for clmn 1 & 1,2,3,4,5 for clmn 2.. I hope it makes sense.

Regards,
Dennis