none
Concatenating results

    Question

  • Here's the scenario. I'm writing a query to summarize the results of a survey. The survey data is in a SQL table. One of the questions allows the user to choose up to ten answers. Here's a decidedly fake example:

    Who do you think should win the prize?

    [] Joe Jones

    [] Billy Brazos

    [] Nancy Ninja

    etc....

    There are ten names to choose from. Survey takers can choose any one or combination of names. Each name is a field in the table that contains False if the name is not checked or True if it is checked. 

    My mission is to collect all of the trues in the record, replace the 'true' with the answer that it represents, then concatenate those answers into a single string. So, using my above example, if the record looks like this:

    Question                                                   Joe Jones   Billy Brazos  Nancy Ninja

    Who do you think should win the prize?   true            false            true

    The query needs to return:

    Joe Jones/Nancy Ninja

    YIKES!! How do I do this?

    Let me know if you need more info, and thanx for any replies.

    tod

     

    Thursday, November 14, 2013 11:02 PM

Answers

  • SELECT Q1__c AS 'Question 1',
    stuff ( 
    case when Q1a_Data__c = 'true' then ', Answ1' else '' end + 
    case when Q1b_Data__c = 'true' then ', Answ2' else '' end + 
    case when Q1c_Data__c = 'true' then ', Answ3' else '' end + 
    case when Q1d_Data__c = 'true' then ', Answ4' else '' end + 
    case when Q1e_Data__c = 'true' then ', Answ5' else '' end + 
    case when Q1f_Data__c = 'true' then ', Answ6' else '' end + 
    case when Q1g_Data__c = 'true' then ', Answ7' else '' end + 
    case when Q1h_Data__c = 'true' then ', Answ8' else '' end + 
    case when Q1i_Data__c = 'true' then ', Answ9' else '' end + 
    case when Q1j_Data__c = 'true' then ', Answ10' else '' end 1,2, '') as Answer
     FROM 
    Survey_Data


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by todtown Friday, November 15, 2013 4:10 PM
    Friday, November 15, 2013 2:46 PM

All replies

  • How are you going to store your results?

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, November 14, 2013 11:07 PM
  • How are you going to store your results?

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    The results of the query will be copied to an Excel worksheet. In VBA, I'm using an ADO connection to the database to query the table and then return the results in a Recordset. Then copy the Recordset to a column in the worksheet. 

    tod

    Thursday, November 14, 2013 11:17 PM
  • Hi,

    Please try this (you may need to make your custom changes).

    USE tempdb;
    GO
    
    -- Declares dummy table
    DECLARE @Survey TABLE (Question VARCHAR(100), Answer1 BIT, Answer2 BIT, Answer3 BIT, Answer4 BIT, Answer5 BIT, Answer6 BIT, Answer7 BIT, Answer8 BIT, Answer9 BIT, Answer10 BIT);
    INSERT INTO @Survey VALUES
    	(
    		'Who do you think should win the prize?',
    		1,
    		0,
    		1,
    		0,
    		0,
    		0,
    		1,
    		0,
    		0,
    		0
    	);
    
    -- Returns Results
    SELECT Question, CASE WHEN LEN(Answer)>0 THEN SUBSTRING(Answer, 1, LEN(Answer)-1) ELSE '' END AS Answer
    FROM
    (
    	SELECT Question,
    		CONCAT(
    			CASE WHEN Answer1 = 1 THEN 'Joe Jones/' ELSE '' END,
    			CASE WHEN Answer2 = 1 THEN 'Billy Brazos/' ELSE '' END,
    			CASE WHEN Answer3 = 1 THEN 'Nancy Ninja/' ELSE '' END,
    			CASE WHEN Answer4 = 1 THEN 'Answer4/' ELSE '' END,
    			CASE WHEN Answer5 = 1 THEN 'Answer5/' ELSE '' END,
    			CASE WHEN Answer6 = 1 THEN 'Answer6/' ELSE '' END,
    			CASE WHEN Answer7 = 1 THEN 'Answer7/' ELSE '' END,
    			CASE WHEN Answer8 = 1 THEN 'Answer8/' ELSE '' END,
    			CASE WHEN Answer9 = 1 THEN 'Answer9/' ELSE '' END,
    			CASE WHEN Answer10 = 1 THEN 'Answer10/' ELSE '' END
    		) AS Answer
    		FROM @Survey
    ) Survey;

    Hope this helps,

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Pedro Martins
    Portugal
    https://www.linkedin.com/in/rechousa

    Thursday, November 14, 2013 11:47 PM
  • Please post DDL.


    Regards, RSingh

    Friday, November 15, 2013 3:28 AM
  • todtown,

    Please post your DDL. Without that it is very difficult to visualize the way you are storing the data at backend. When user checks the checkbox in front of any answer. What happens to the table values?


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, November 15, 2013 7:30 AM
  • todtown,

    Please post your DDL. Without that it is very difficult to visualize the way you are storing the data at backend. When user checks the checkbox in front of any answer. What happens to the table values?


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    The user is completing a survey online. Those results are imported to a database table. I'm writing a query against that database table and returning the results to a worksheet. 

    For this particular question, the user is presented with ten possible answers that they can check. The user can check one, none or any combination of the ten. Each answer is recorded in it's own field in the table as true or false. So...if the table name is Survey_Data, and I just want to return all applicable fields, the query looks like this:

    SELECT
    Q1__c AS 'Question 1',
    Q1a_Data__c AS Answ1, 
    Q1b_Data__c AS Answ2, 
    Q1c_Data__c AS Answ3, 
    Q1d_Data__c AS Answ4, 
    Q1e_Data__c AS Answ5, 
    Q1f_Data__c AS Answ6, 
    Q1g_Data__c AS Answ7, 
    Q1h_Data__c AS Answ8, 
    Q1i_Data__c AS Answ9, 
    Q1j_Data__c AS Answ10
    FROM 
    Survey_Data

    And a sample record would look like this:

    Question 1                 Answ1 Answ2 Answ3 Answ4 Answ5 Answ6 Answ7 Answ8 Answ9 Answ10
    Who are you inviting?true     true     false    true     true     false    false     false    false   false

    So in this example the user has checked Answ1, Answ2 and Answ4. 

    I want my query to concatenate all of the answers into a string. Like this:

    Answ1/Answ2/Answ4

    Maybe I can't do all of this is one query. Maybe I need an interim step. I'm still to try the code sent by Pedro. If anybody else has an idea then please respond.

    tod

    Friday, November 15, 2013 2:37 PM
  • SELECT Q1__c AS 'Question 1',
    stuff ( 
    case when Q1a_Data__c = 'true' then ', Answ1' else '' end + 
    case when Q1b_Data__c = 'true' then ', Answ2' else '' end + 
    case when Q1c_Data__c = 'true' then ', Answ3' else '' end + 
    case when Q1d_Data__c = 'true' then ', Answ4' else '' end + 
    case when Q1e_Data__c = 'true' then ', Answ5' else '' end + 
    case when Q1f_Data__c = 'true' then ', Answ6' else '' end + 
    case when Q1g_Data__c = 'true' then ', Answ7' else '' end + 
    case when Q1h_Data__c = 'true' then ', Answ8' else '' end + 
    case when Q1i_Data__c = 'true' then ', Answ9' else '' end + 
    case when Q1j_Data__c = 'true' then ', Answ10' else '' end 1,2, '') as Answer
     FROM 
    Survey_Data


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by todtown Friday, November 15, 2013 4:10 PM
    Friday, November 15, 2013 2:46 PM
  • That's it! Although, I modified your code just a bit. The only problem was that the result has a single quote at the beginning of the string. So I need to remove the first character of the result. (I also replaced the single quote + space with a forward slash and no space.  Feel free to shrink this down. 

    SELECT Right(case when s.Q1a_Data__c = 'true' then '/Answ1' else '' end + 
    case when Q1b_Data__c = 'true' then '/Answ2' else '' end + 
    case when Q1c_Data__c = 'true' then '/Answ3' else '' end + 
    case when Q1d_Data__c = 'true' then '/Answ4' else '' end + 
    case when Q1e_Data__c = 'true' then '/Answ5' else '' end + 
    case when Q1f_Data__c = 'true' then '/Answ6' else '' end + 
    case when Q1g_Data__c = 'true' then '/Answ7' else '' end + 
    case when Q1h_Data__c = 'true' then '/Answ8' else '' end + 
    case when Q1i_Data__c = 'true' then '/Answ9' else '' end + 
    case when Q1j_Data__c = 'true' then '/Answ10' else '' end,LEN(case when Q1a_Data__c = 'true' then '/Answ1' else '' end + 
    case when Q1b_Data__c = 'true' then '/Answ2' else '' end + 
    case when Q1c_Data__c = 'true' then '/Answ3' else '' end + 
    case when Q1d_Data__c = 'true' then '/Answ4' else '' end + 
    case when Q1e_Data__c = 'true' then '/Answ5' else '' end + 
    case when Q1f_Data__c = 'true' then '/Answ6' else '' end + 
    case when Q1g_Data__c = 'true' then '/Answ7' else '' end + 
    case when Q1h_Data__c = 'true' then '/Answ8' else '' end + 
    case when Q1i_Data__c = 'true' then '/Answ9' else '' end + 
    case when Q1j_Data__c = 'true' then '/Answ10' else '' end)-1) AS 'Who are you inviting?'
     FROM 
    Survey_Data

    Friday, November 15, 2013 4:10 PM
  • Oh wait. A coworker showed me the SubString function. So..I'm good now! Thanx everybody.

    Here's the final query:

    SELECT RTRIM(SUBSTRING(CASE WHEN Q1a_Data__c = 'true' THEN '/Answ1' ELSE '' END + 
    CASE WHEN Q1b_Data__c = 'true' THEN '/Answ2' ELSE '' END + 
    CASE WHEN Q1c_Data__c = 'true' THEN '/Answ3' ELSE '' END + 
    CASE WHEN Q1d_Data__c = 'true' THEN '/Answ4' ELSE '' END + 
    CASE WHEN Q1e_Data__c = 'true' THEN '/Answ5' ELSE '' END + 
    CASE WHEN Q1f_Data__c = 'true' THEN '/Answ6' ELSE '' END + 
    CASE WHEN Q1g_Data__c = 'true' THEN '/Answ7' ELSE '' END + 
    CASE WHEN Q1h_Data__c = 'true' THEN '/Answ8' ELSE '' END + 
    CASE WHEN Q1i_Data__c = 'true' THEN '/Answ9' ELSE '' END + 
    CASE WHEN Q1j_Data__c = 'true' THEN '/Answ10' ELSE '' END,2,1000)) AS 'Who are you inviting?'
     FROM 
    Survey_Data

    Friday, November 15, 2013 4:25 PM
  •  
    CREATE TABLE Survey_Data  (Question VARCHAR(100), Answ1    BIT, Answ2    BIT, Answ3    BIT, Answ4    BIT,  Answ5    BIT
    ,  Answ6    BIT,  Answ7    BIT,   Answ8    BIT,   Answ9    BIT,   Answ10   BIT);
    INSERT INTO Survey_Data
    VALUES('Who do you think should win the prize?',1,1,0,1,1,0,0,0,0,0 ),
    ('Another question?',0,1,0,0,1,0,0,0,0,1 );  
    ;with mycte as (SELECT question,val,col
    FROM   Survey_Data
    UNPIVOT (val for col IN (Answ1, Answ2, Answ3, Answ4, Answ5, Answ6, Answ7, Answ8, Answ9, Answ10) )unpvt
    WHERE val=1
    )
    	 
    						  
    SELECT   question, stuff((select ',' + m1.col
    from mycte m1 WHERE m.question=m1.question  for XML PATH('')),1,1,'') as [Who are you inviting?] 
    from mycte m 
    Group By question
    --Or 
    ;with mycte1 as (SELECT question,val,col
    FROM   Survey_Data
           CROSS apply (VALUES (Answ1,'Answ1'),
                               (Answ2,'Answ2'),
                               (Answ3,'Answ3'),
                               (Answ4,'Answ4'),
                               (Answ5,'Answ5'),
                               (Answ6,'Answ6'),
                               (Answ7,'Answ7'),
                               (Answ8,'Answ8'),
                               (Answ9,'Answ9'),
                               (Answ10,'Answ10') ) d(val, col) 
    						   WHERE val=1)
    						  
    SELECT   question, stuff((select ',' + m1.col
    from mycte1 m1 WHERE m.question=m1.question  for XML PATH('')),1,1,'') as [Who are you inviting?] 
    from mycte1 m 
    Group By question
    Drop table Survey_Data

    Friday, November 15, 2013 10:16 PM