Convert single field SQL output to different fields

Question

I have a business requirement to modify the SQL output to different fields.

Example, I have a table SurveyResults and logs questions and answers in single column.

select * from SurveyResults

Output:

+-----------------+-------+------------+-----------------------+-----------+------------+-------------------------+-------------------------+------------------------------------------------+-----------------------------------------------------------------------------------------------------+
| ApplicationName | DNIS  |  Provider  | AgentPeripheralNumber | PlanName  | CaseNumber |        StartTime        |         EndTime         |                     CallId                     |                                               Results                                               |
+-----------------+-------+------------+-----------------------+-----------+------------+-------------------------+-------------------------+------------------------------------------------+-----------------------------------------------------------------------------------------------------+
| AgentSurvey     | 20xxx | 860xxxxxxx |                 2xxxx | xxxxx     | NULL       | 2018-08-16 13:33:05.390 | 2018-08-16 13:33:22.127 | 10.xxx.xxx.xx.1534440785204.634158.AgentSurvey | Q1:null;Q2:null;Q3:null;Q4:null;Q4a:null;Q5:null;Q6:null;Q6a:null;Q7:null;Q8:null;Q9:null;Q10:null; |
| AgentSurvey     | 20xxx | 508xxxxxxx |                 2xxxx | xxxxxx    | NULL       | 2018-08-16 13:32:50.500 | 2018-08-16 13:33:11.643 | 10.xxx.xxx.xx.1534440770297.634125.AgentSurvey | Q1:null;Q2:null;Q3:null;Q4:null;Q4a:null;Q5:null;Q6:null;Q6a:null;Q7:null;Q8:null;Q9:null;Q10:null; |
| AgentSurvey     | 20xxx | 408xxxxxxx |                 2xxxx | xxxxxxxxx | D1096xxxxx | 2018-08-16 13:32:39.240 | 2018-08-16 13:32:57.630 | 10.xxx.xxx.xx.1534440759224.640875.AgentSurvey | Q1:null;Q2:null;Q3:null;Q4:null;Q4a:null;Q5:null;Q6:null;Q6a:null;Q7:null;Q8:null;Q9:null;Q10:null; |
| AgentSurvey     | 20xxx | 860xxxxxxx |                 2xxxx | xxxxx     | NULL       | 2018-08-16 13:32:12.217 | 2018-08-16 13:32:14.310 | 10.xxx.xxx.xx.1534440732030.634018.AgentSurvey | Q1:null;Q2:null;Q3:null;Q4:null;Q4a:null;Q5:null;Q6:null;Q6a:null;Q7:null;Q8:null;Q9:null;Q10:null; |
+-----------------+-------+------------+-----------------------+-----------+------------+-------------------------+-------------------------+------------------------------------------------+-----------------------------------------------------------------------------------------------------+

Now business wants to view this data in multiple columns. something like below.

Output:

Provider DNIS Q1 Q2 Q3 Q4

XXXXXX 2XXXX Phy.. sat.. oth.. res.. XXXXXX 2XXXX phy.. case.. null

I can do this by simply modifying my table but I can't do that as it will be tedious task as its dependent on different application.

Used your query:

SELECT sr.CallId,sr.PlanName,sr.Provider,sr.CaseNumber,sr.StartTime
    , NULLIF( qn.qnXml.value( 'data(//qn[name="Q1"]/val)[1]', 'VARCHAR(50)' ), 'null' ) AS Q1
    , NULLIF( qn.qnXml.value( 'data(//qn[name="Q2"]/val)[1]', 'VARCHAR(50)' ), 'null' ) AS Q2
    , NULLIF( qn.qnXml.value( 'data(//qn[name="Q3"]/val)[1]', 'VARCHAR(50)' ), 'null' ) AS Q3
    , NULLIF( qn.qnXml.value( 'data(//qn[name="Q4"]/val)[1]', 'VARCHAR(50)' ), 'null' ) AS Q4
    , NULLIF( qn.qnXml.value( 'data(//qn[name="Q4a"]/val)[1]', 'VARCHAR(50)' ), 'null' ) AS Q4a
    , NULLIF( qn.qnXml.value( 'data(//qn[name="Q5"]/val)[1]', 'VARCHAR(50)' ), 'null' ) AS Q5
    , NULLIF( qn.qnXml.value( 'data(//qn[name="Q6"]/val)[1]', 'VARCHAR(50)' ), 'null' ) AS Q6
    , NULLIF( qn.qnXml.value( 'data(//qn[name="Q6a"]/val)[1]', 'VARCHAR(50)' ), 'null' ) AS Q6a
    , NULLIF( qn.qnXml.value( 'data(//qn[name="Q7"]/val)[1]', 'VARCHAR(50)' ), 'null' ) AS Q7
    , NULLIF( qn.qnXml.value( 'data(//qn[name="Q8"]/val)[1]', 'VARCHAR(50)' ), 'null' ) AS Q8
    , NULLIF( qn.qnXml.value( 'data(//qn[name="Q9"]/val)[1]', 'VARCHAR(50)' ), 'null' ) AS Q9
    , NULLIF( qn.qnXml.value( 'data(//qn[name="Q10"]/val)[1]', 'VARCHAR(50)' ), 'null' ) AS Q10
FROM SurveyResults sr
CROSS APPLY (

    -- convert [Results] to parsable xml --

    SELECT 
        CAST( '<qn><name>' + REPLACE( REPLACE( LEFT( Results, LEN( Results ) - 1 ), ':', '</name><val>' ), ';', '</val></qn><qn><name>' ) + '</val></qn>' AS XML ) AS qnXml
    FROM SurveyResults where [CallId] = sr.CallId

) AS qn
ORDER BY
StartTime desc;

Getting this error now: Msg 9411, Level 16, State 1, Line 1 XML parsing: line 1, character 45, semicolon expected

From the screenshot I guess this is SMSS hence SQL Server. Please correct the tag, if I guessed wrong. — sticky bit, Aug 15 '18 at 18:17
You may want to look at: https://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x — Koen, Aug 15 '18 at 18:22
Tab Alleman: Sorry that was a typo.. I meant to say by modifying the table. — Manoj Vunnava, Aug 15 '18 at 18:44

critical_error · Answer 1 · 2018-08-16T20:02:16.023

Okay, let me start with the obligatory mentoring comment: You really need to normalize your data.

With that out of the way, this would be my approach to your situation. You can run/modify this example in SSMS.

First thing I did was create a sample environment that ( somewhat ) matched yours:

DECLARE @SurveyResults TABLE ( [Results] VARCHAR(1000), [Id] INT IDENTITY (1,1) );

INSERT INTO @SurveyResults ( [Results] )
VALUES
( 'Q1:null;Q2:very_satisified;Q3:null;Q4:null;Q5:null;Q6:null;Q7:null;Q8:null;Q9:null;Q10:null;' )
, ( 'Q1:null;Q2:null;Q3:null;Q4:null;Q5:null;Q6:null;Q7:null;Q8:null;Q9:null;Q10:null;' )
, ( 'Q1:physician_office;Q2:satisfied;Q3:other_reason;Q4:resolution_no;Q5:timely_yes;Q6:website_yes;Q7:no_reply;Q8:null;Q9:null;Q10:null;' );

Then I queried Results XML from SurveyResults:

SELECT
    sr.Id
    , NULLIF( qn.qnXml.value( 'data(//qn[name="Q1"]/val)[1]', 'VARCHAR(50)' ), 'null' ) AS Q1
    , NULLIF( qn.qnXml.value( 'data(//qn[name="Q2"]/val)[1]', 'VARCHAR(50)' ), 'null' ) AS Q2
    , NULLIF( qn.qnXml.value( 'data(//qn[name="Q3"]/val)[1]', 'VARCHAR(50)' ), 'null' ) AS Q3
    , NULLIF( qn.qnXml.value( 'data(//qn[name="Q4"]/val)[1]', 'VARCHAR(50)' ), 'null' ) AS Q4
    , NULLIF( qn.qnXml.value( 'data(//qn[name="Q5"]/val)[1]', 'VARCHAR(50)' ), 'null' ) AS Q5
    , NULLIF( qn.qnXml.value( 'data(//qn[name="Q6"]/val)[1]', 'VARCHAR(50)' ), 'null' ) AS Q6
    , NULLIF( qn.qnXml.value( 'data(//qn[name="Q7"]/val)[1]', 'VARCHAR(50)' ), 'null' ) AS Q7
    , NULLIF( qn.qnXml.value( 'data(//qn[name="Q8"]/val)[1]', 'VARCHAR(50)' ), 'null' ) AS Q8
    , NULLIF( qn.qnXml.value( 'data(//qn[name="Q9"]/val)[1]', 'VARCHAR(50)' ), 'null' ) AS Q9
    , NULLIF( qn.qnXml.value( 'data(//qn[name="Q10"]/val)[1]', 'VARCHAR(50)' ), 'null' ) AS Q10
FROM @SurveyResults sr
CROSS APPLY (

    -- convert [Results] to parsable xml --

    SELECT 
        CAST( '<qn><name>' + REPLACE( REPLACE( LEFT( Results, LEN( Results ) - 1 ), ':', '</name><val>' ), ';', '</val></qn><qn><name>' ) + '</val></qn>' AS XML ) AS qnXml
    FROM @SurveyResults WHERE [Id] = sr.Id

) AS qn
ORDER BY
    [Id];

Returns:

+----+------------------+-----------------+--------------+---------------+------------+-------------+----------+------+------+------+
| Id |        Q1        |       Q2        |      Q3      |      Q4       |     Q5     |     Q6      |    Q7    |  Q8  |  Q9  | Q10  |
+----+------------------+-----------------+--------------+---------------+------------+-------------+----------+------+------+------+
|  1 | NULL             | very_satisified | NULL         | NULL          | NULL       | NULL        | NULL     | NULL | NULL | NULL |
|  2 | NULL             | NULL            | NULL         | NULL          | NULL       | NULL        | NULL     | NULL | NULL | NULL |
|  3 | physician_office | satisfied       | other_reason | resolution_no | timely_yes | website_yes | no_reply | NULL | NULL | NULL |
+----+------------------+-----------------+--------------+---------------+------------+-------------+----------+------+------+------+

My example does not include your "Q4a" offshoots ( or the like ) and is making some major assumptions ( e.g. every Result has a trailing semi-colon ), however, I'm thinking there's enough here to get you started.

UPDATE BASED ON NEW DETAILS ( 08/16/2018 12:45 PM PST )

/*
+-----------------+-------+------------+-----------------------+-----------+------------+-------------------------+-------------------------+------------------------------------------------+-----------------------------------------------------------------------------------------------------+
| ApplicationName | DNIS  |  Provider  | AgentPeripheralNumber | PlanName  | CaseNumber |        StartTime        |         EndTime         |                     CallId                     |                                               Results                                               |
+-----------------+-------+------------+-----------------------+-----------+------------+-------------------------+-------------------------+------------------------------------------------+-----------------------------------------------------------------------------------------------------+
| AgentSurvey     | 20xxx | 860xxxxxxx |                 2xxxx | xxxxx     | NULL       | 2018-08-16 13:33:05.390 | 2018-08-16 13:33:22.127 | 10.xxx.xxx.xx.1534440785204.634158.AgentSurvey | Q1:null;Q2:null;Q3:null;Q4:null;Q4a:null;Q5:null;Q6:null;Q6a:null;Q7:null;Q8:null;Q9:null;Q10:null; |
| AgentSurvey     | 20xxx | 508xxxxxxx |                 2xxxx | xxxxxx    | NULL       | 2018-08-16 13:32:50.500 | 2018-08-16 13:33:11.643 | 10.xxx.xxx.xx.1534440770297.634125.AgentSurvey | Q1:null;Q2:null;Q3:null;Q4:null;Q4a:null;Q5:null;Q6:null;Q6a:null;Q7:null;Q8:null;Q9:null;Q10:null; |
| AgentSurvey     | 20xxx | 408xxxxxxx |                 2xxxx | xxxxxxxxx | D1096xxxxx | 2018-08-16 13:32:39.240 | 2018-08-16 13:32:57.630 | 10.xxx.xxx.xx.1534440759224.640875.AgentSurvey | Q1:null;Q2:null;Q3:null;Q4:null;Q4a:null;Q5:null;Q6:null;Q6a:null;Q7:null;Q8:null;Q9:null;Q10:null; |
| AgentSurvey     | 20xxx | 860xxxxxxx |                 2xxxx | xxxxx     | NULL       | 2018-08-16 13:32:12.217 | 2018-08-16 13:32:14.310 | 10.xxx.xxx.xx.1534440732030.634018.AgentSurvey | Q1:null;Q2:null;Q3:null;Q4:null;Q4a:null;Q5:null;Q6:null;Q6a:null;Q7:null;Q8:null;Q9:null;Q10:null; |
+-----------------+-------+------------+-----------------------+-----------+------------+-------------------------+-------------------------+------------------------------------------------+-----------------------------------------------------------------------------------------------------+
*/

-- create table variable --

DECLARE @SurveyResults TABLE ( ApplicationName VARCHAR(50), DNIS VARCHAR(10), [Provider] VARCHAR(10), AgentPeripheralNumber VARCHAR(255), PlanName VARCHAR(10), CaseNumber VARCHAR(10), StartTime DATETIME, EndTime DATETIME, CallId VARCHAR(255), Results VARCHAR(1000) );

-- insert data --

INSERT INTO @SurveyResults (
    ApplicationName, DNIS, [Provider], AgentPeripheralNumber, PlanName, CaseNumber, StartTime, EndTime, CallId, Results
)
VALUES
  ( 'AgentSurvey', '20xxx', '860xxxxxxx', '                2xxxx', 'xxxxx', NULL, '2018-08-16 13:33:05.390', '2018-08-16 13:33:22.127', '10.xxx.xxx.xx.1534440785204.634158.AgentSurvey', 'Q1:null;Q2:null;Q3:null;Q4:null;Q4a:null;Q5:null;Q6:null;Q6a:null;Q7:null;Q8:null;Q9:null;Q10:null;' )
, ( 'AgentSurvey', '20xxx', '508xxxxxxx', '                2xxxx', 'xxxxxx', NULL, '2018-08-16 13:32:50.500', '2018-08-16 13:33:11.643', '10.xxx.xxx.xx.1534440770297.634125.AgentSurvey', 'Q1:null;Q2:null;Q3:null;Q4:null;Q4a:null;Q5:null;Q6:null;Q6a:null;Q7:null;Q8:null;Q9:null;Q10:null;' )
, ( 'AgentSurvey', '20xxx', '408xxxxxxx', '                2xxxx', 'xxxxxxxxx', 'D1096xxxxx', '2018-08-16 13:32:39.240', '2018-08-16 13:32:57.630', '10.xxx.xxx.xx.1534440759224.640875.AgentSurvey', 'Q1:null;Q2:null;Q3:null;Q4:null;Q4a:null;Q5:null;Q6:null;Q6a:null;Q7:null;Q8:null;Q9:null;Q10:null;' )
, ( 'AgentSurvey', '20xxx', '860xxxxxxx', '                2xxxx', 'xxxxx', NULL, '2018-08-16 13:32:12.217', '2018-08-16 13:32:14.310', '10.xxx.xxx.xx.1534440732030.634018.AgentSurvey', 'Q1:null;Q2:null;Q3:null;Q4:null;Q4a:null;Q5:null;Q6:null;Q6a:null;Q7:null;Q8:null;Q9:null;Q10:null;' )

-- preview data --

SELECT * FROM @SurveyResults ORDER BY StartTime;

-- query data --
-- removed NULLIFs for the sake of readability --

SELECT
    sr.CallId
    , qn.qnXml.value( 'data(//qn[name="Q1"]/val)[1]', 'VARCHAR(50)' ) AS Q1
    , qn.qnXml.value( 'data(//qn[name="Q1a"]/val)[1]', 'VARCHAR(50)' ) AS Q1a
    , qn.qnXml.value( 'data(//qn[name="Q2"]/val)[1]', 'VARCHAR(50)' ) AS Q2
    , qn.qnXml.value( 'data(//qn[name="Q2a"]/val)[1]', 'VARCHAR(50)' ) AS Q2a
    , qn.qnXml.value( 'data(//qn[name="Q3"]/val)[1]', 'VARCHAR(50)' ) AS Q3
    , qn.qnXml.value( 'data(//qn[name="Q3a"]/val)[1]', 'VARCHAR(50)' ) AS Q3a
    , qn.qnXml.value( 'data(//qn[name="Q4"]/val)[1]', 'VARCHAR(50)' ) AS Q4
    , qn.qnXml.value( 'data(//qn[name="Q4a"]/val)[1]', 'VARCHAR(50)' ) AS Q4a
    , qn.qnXml.value( 'data(//qn[name="Q5"]/val)[1]', 'VARCHAR(50)' ) AS Q5
    , qn.qnXml.value( 'data(//qn[name="Q5a"]/val)[1]', 'VARCHAR(50)' ) AS Q5a
    , qn.qnXml.value( 'data(//qn[name="Q6"]/val)[1]', 'VARCHAR(50)' ) AS Q6
    , qn.qnXml.value( 'data(//qn[name="Q6a"]/val)[1]', 'VARCHAR(50)' ) AS Q6a
    , qn.qnXml.value( 'data(//qn[name="Q7"]/val)[1]', 'VARCHAR(50)' ) AS Q7
    , qn.qnXml.value( 'data(//qn[name="Q7a"]/val)[1]', 'VARCHAR(50)' ) AS Q7a
    , qn.qnXml.value( 'data(//qn[name="Q8"]/val)[1]', 'VARCHAR(50)' ) AS Q8
    , qn.qnXml.value( 'data(//qn[name="Q8a"]/val)[1]', 'VARCHAR(50)' ) AS Q8a
    , qn.qnXml.value( 'data(//qn[name="Q9"]/val)[1]', 'VARCHAR(50)' ) AS Q9
    , qn.qnXml.value( 'data(//qn[name="Q9a"]/val)[1]', 'VARCHAR(50)' ) AS Q9a
    , qn.qnXml.value( 'data(//qn[name="Q10"]/val)[1]', 'VARCHAR(50)' ) AS Q10
    , qn.qnXml.value( 'data(//qn[name="Q10a"]/val)[1]', 'VARCHAR(50)' ) AS Q10a
FROM @SurveyResults sr
CROSS APPLY (
    -- convert [Results] to parsable xml --

    SELECT 
        CAST( '<qn><name>' + REPLACE( REPLACE( LEFT( Results, LEN( Results ) - 1 ), ':', '</name><val>' ), ';', '</val></qn><qn><name>' ) + '</val></qn>' AS XML ) AS qnXml
    FROM @SurveyResults where [CallId] = sr.CallId

) qn
ORDER BY
    sr.StartTime;

Returns ( 4 rows only, as expected ):

+------------------------------------------------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
|                     CallId                     |  Q1  | Q1a  |  Q2  | Q2a  |  Q3  | Q3a  |  Q4  | Q4a  |  Q5  | Q5a  |  Q6  | Q6a  |  Q7  | Q7a  |  Q8  | Q8a  |  Q9  | Q9a  | Q10  | Q10a |
+------------------------------------------------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
| 10.xxx.xxx.xx.1534440732030.634018.AgentSurvey | null | NULL | null | NULL | null | NULL | null | null | null | NULL | null | null | null | NULL | null | NULL | null | NULL | null | NULL |
| 10.xxx.xxx.xx.1534440759224.640875.AgentSurvey | null | NULL | null | NULL | null | NULL | null | null | null | NULL | null | null | null | NULL | null | NULL | null | NULL | null | NULL |
| 10.xxx.xxx.xx.1534440770297.634125.AgentSurvey | null | NULL | null | NULL | null | NULL | null | null | null | NULL | null | null | null | NULL | null | NULL | null | NULL | null | NULL |
| 10.xxx.xxx.xx.1534440785204.634158.AgentSurvey | null | NULL | null | NULL | null | NULL | null | null | null | NULL | null | null | null | NULL | null | NULL | null | NULL | null | NULL |
+------------------------------------------------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+

This works for me.

Almost got it. My tables has about 14k result but when I run your query I'm getting 453k results.. — Manoj Vunnava, Aug 16 '18 at 16:48
Are you sure you are using CROSS APPLY and not OUTER APPY or just APPLY? It would help if I could see some data from you SurveyResults table too. — critical_error, Aug 16 '18 at 16:50
Here is my actual table contents. CallId being primary key. But when I run your query I'm getting duplicates of it with different results. Very weird. — Manoj Vunnava, Aug 16 '18 at 16:59
Select * from SurveyResults ApplicationName DNIS Provider AgentPeripheralNumber PlanName CaseNumber StartTime EndTime CallId Results AgentSurvey 10138 4059652234 13695 VVERA A105XXXXXX 2018-08-16 12:48:35.050 2018-08-16 12:48:40.737 10.XXX.XXX.XX.1534438114846.627219.AgentSurvey Q1:null;Q2:null;Q3:null;Q4:null;Q4a:null;Q5:null;Q6:null;Q6a:null;Q7:null;Q8:null;Q9:null;Q10:null; — Manoj Vunnava, Aug 16 '18 at 17:00
I'm unable to add the complete query and results over here.. How do I do it? Sorry I'm new to this portal. — Manoj Vunnava, Aug 16 '18 at 17:03
Please confirm you are using "CROSS APPLY"? Do you have a Unique ID that guarantees the CROSS APPLY's "WHERE [id] =[sr].[id]" is unique? If not, this could return multiple rows given that each result has a "Q1" ect… Hence your current experience. — critical_error, Aug 16 '18 at 17:08
Edit your question to include the additional information. You can use https://senseful.github.io/text-table/ to help format your results so they are easier to interpret. — critical_error, Aug 16 '18 at 17:11
Updated my example based on the new data you provided, @ManojVunnava. — critical_error, Aug 16 '18 at 19:57
Don't forget to mark as the answer if this worked for you. :) — critical_error, Aug 16 '18 at 21:20

Convert single field SQL output to different fields

Provider DNIS Q1 Q2 Q3 Q4

1 Answers1