Stored Procedure for converting rows to columns in SQL Server

Question

I'm looking for an efficient way of how to convert rows to columns in SQL Server. I tried in Toad for Oracle, but now I want it in SQL Server.

This is my example:

CID   SENTENCE
1   Hello; Hi;
2   Why; What;

The result should be like

CID   SENTENCE

1   Hello
1   Hi
2   Why
2   What

Would you please help me with it?

Answer 1

I would advise you to rethink your database design. It's almost never a good idea to store data in a delimited string in any relational database.
If it's impossible to change your database design, you need to use some UDF to split strings.
There are many different approaches to split strings in sql server, read this article on the differences between the common ways.

You can probably change your chosen split string function to take the cid as well as the sentence as a variable and have it return the data exactly as your desired output for each row in your table.
Then all you have to do is a select from your table with an inner join with the udf on the cid.

Answer 2

try

  declare @var table (CID int,   SENTENCE  varchar(50))
    insert into @var(CID,SENTENCE) values
    (1,'Hello; Hi;'),
    (2,'Why; What;')

    select cid,t.c.value('.','varchar(50)') as val 
from (select cid,x=cast('<t>'+ replace(stuff(sentence,len(sentence),1,''),';','</t><t>')+'</t>'  as xml) 
 from @var) a cross apply x.nodes('/t')  t(c)