I've got a column in a database table that is stored like the below that I'm trying to split out into columns to be able to report on it a lot easier:

  Question 1 : ["Yes","","b1"];  Question 2 : ["","No","b2"];  Question 3: ["Yes","","b3"];  Question 4: ["","No",""];  Question 5: ["Yes","","b5"];  Question 6: ["","No","b6"];  Question 7: ["Yes","","b7"];  

From the below table, I've got this so far:

SELECT
    *,
    CASE 
        WHEN LEN(ItemValues) > 1 THEN 
            LEFT(ItemValues, charindex(':', ItemValues) - 1)
    END as Question,
    '' as Answer,
    '' as Comment
FROM 
(
    SELECT 
        ID,
        TRIM(value) as ItemValues
    FROM 
        #StackQuestion
    CROSS APPLY STRING_SPLIT(Response,';')
) t1
where
    LEN(ItemValues) > 1

What I'm really struggling with is populating the Answer and Comment columns. The Answer column should contain "Yes" or "No", and the Comment column should contain the final part which is b1 for example.

Any ideas?

Create Table #StackQuestion
(
    ID int IDENTITY(1,1), 
    Response varchar(2000) 
)

insert into #StackQuestion
(
    Response
)
select
'
Question 1 : ["Yes","","b1"]; 
Question 2 : ["","No","b2"]; 
Question 3: ["Yes","","b3"]; 
Question 4: ["","No",""]; 
Question 5: ["Yes","","b5"]; 
Question 6: ["","No","b6"]; 
Question 7: ["Yes","","b7"]; 
'
union all
select
'
Question 1 : ["","No","comment1"]; 
Question 2 : ["","No","c2"]; 
Question 3: ["Yes","","c3"]; 
Question 4: ["Yes","","c4"]; 
Question 5: ["Yes","","b5"]; 
Question 6: ["","No","b6"]; 
Question 7: ["Yes","","b7"]; 
'

CodePudding user response：

I had a quick play around with some additional string manipulation, perhaps something like the following will work for you?

select ID, 
  Trim(Left(ItemValues, CharIndex(':', ItemValues) - 1)) as Question,
  case when ItemValues like '%"Yes"%' then 'Yes' else 'No' end as Answer,
  comment
from #StackQuestion
cross apply (
  select value as ItemValues, Reverse(Left(c, CharIndex('"',c) -1)) comment
  from String_Split(Response, ';')
  cross apply(values(Reverse(Replace(value, '"]',''))))v(c)
  where Len(value) > 3
)r;

Note that although in practice you will probably be ok, using string split is documented to not be relied upon for ordering.

CodePudding user response：

Since the answers list to each of the questions is actually valid JSON syntax, you can use the OPENJSON function, which will output the answers as different records. The PIVOT operator can be used to get the values on the same line again.

Here is an adaptation of the code that does the job. Note that I replaced the len(ItemValues) > 1 condition with charindex(':', ItemValues) >= 1 in order to get rid of errors on white space strings that were longer than 1 character.

with prep as (
    SELECT
        *,
        LEFT(ItemValues, charindex(':', ItemValues) - 1) as Question,
        substring(ItemValues, charindex(':', ItemValues)   1, 999) as Answers
    FROM 
    (
        SELECT 
            ID
            ,
            TRIM(value) as ItemValues
            --ca1.ItemValues
        FROM 
            #StackQuestion
        CROSS APPLY STRING_SPLIT(Response,';') -- ca1(ItemValues)
    ) t1
    where
        charindex(':', ItemValues) >= 1
)
select
    answer = [0]   [1],
    comment = [2]
from (
    SELECT
        * 
    from prep
    cross apply OPENJSON(Answers)
) as answers
pivot ( 
    max([value]) 
    for [key] in ([0], [1], [2])
) piv

Output: