Convert the string to multiple column

Transact-SQL
1

Hi All,

I am having one requirement where In my sql table one of my column having value like question and answer combination.

given the screen shot of input and output data set.

Please use the below query.

I want to convert key value in different column
create table qa
(
id int,
qa nvarchar(max)
)

INSERT INTO qa
SELECT 100,'Q: What is your name ?
A: Ryan

Q: What is your age ?
A: 35'

image

Pl

Thanks,
Vipin jha

2 
SELECT 
      ID
   , replace(value ,': W','Q: W')
FROM 
   qa  
    CROSS APPLY STRING_SPLIT(qa, 'Q')
WHERE value <> ''

image

3

Thanks Harish for your quick help, can I get the question which start with Q in one column and answer in another column like below screen shot.
and one more thing you have consider one think like all question will start with 'W'
but we can have many more question which can start with How, could

image

Thanks,
Vipin Jha

4

I can manage to achieve above concern with below code

image
image893×202 5.66 KB

I want to get Q in one column and A in another column

Thanks,
Vipin

5 
; with cte as 
(
SELECT 
      ID
   ,  value
FROM 
   qa  
    CROSS APPLY STRING_SPLIT(qa, 'Q')
WHERE value <> ''
) 
select 
   id
  ,replace(left(value,patindex('%A:%',value)-2),':','')
  ,replace(right(value,len(value)-patindex('%A:%',value)),':','') 
from 
  cte

image

6

Thank you so much Harish for your help.

7

Hi Harish,

need small help , the logic which given by you worked fine where we dont have any "Q" in questiion and in answer. but it is failing when q comes in question and answer.

please use below sample data for reference

create table qa
(
id int,
qa nvarchar(max)
)

INSERT INTO qa
SELECT 500,'
Q: Foi a primeira vez que utilizou o produto?
A: Não
Q: Quando você observou o problema?
A: Ao abrir o produto
Q: Onde você armazenou o produto? E por quanto tempo?
A: No armário antes do consumo
Q: O produto estava devidamente lacrado quando você o comprou?
A: Sim
Q: Como você consumiu o produto?
A: No estado em que se encontra
Q: Como você preparou o produto?
A: De acordo com as instruções de embalagem
Q: Você adicionou algo ao produto?
A: Não'

Thanks,
Vipin Jha

8

first add in the string
some rare character

so
Q becomes $Q

now use $ to split

issue of Q should go away

9

Answered here