SQL - Array as part of selected data

Question

I am trying to retrieve a survey-dataset from two different tables in mssql. Every survey has a few options for answering, which should be returned in an array as a part of the retrieved data table.

Basically, the returned data table should be like that:

SurveyName xyz        (from table no.1)
SurveyTopic abc       (from table no.1)
Optionen [d, v, q, o] (from table no.2)

What is the best way of achieving this? Creating a defined data type? I tried using a join-argument but then the data gets mixed up and returned as if there were different surveys with one option each. The code looks like this so far:

CREATE PROCEDURE dbo.GetSurvey
    @Id nvarchar (128)
AS
    SELECT Surveys.*, Options,*
    FROM dbo.Surveys
    JOIN Options
    ON Options.SurveyId = Survey.Id
    WHERE Surveys.Id = @Id

Here is some sample data as json-files:

Surveys

{
    "id": "3f07153f-78cf-4b03-a442-5dbbbdc6c85d",
    "topic": "Internet connection",
    "question": "How fast is you connection?"
}

Option (each survey has a few of those)

{
    "id":"3b3f9583-7d09-49d2-baee-d724c6ce3d9d",
    "surveyId":"3f07153f-78cf-4b03-a442-5dbbbdc6c85d",
    "answer":"10mbit/s"
}

What the table should look like:

{
    "id": "3f07153f-78cf-4b03-a442-5dbbbdc6c85d",
    "topic": "Internet connection",
    "question": "How fast is you connection?"
    "options":[
        {"id":"85c1ae87-7da9-41c4-9046-22df231af6ec",
         "surveyId":"3f07153f-78cf-4b03-a442-5dbbbdc6c85d",
         "answer":"10mbit/s"}

        {"id":"55347f11-c01f-4b5f-86a3-9d9c66c2aef5",
         "surveyId":"3f07153f-78cf-4b03-a442-5dbbbdc6c85d",
         "answer":"20mbit/s"}
     ]        
}

Hey Michael can you please provide some sample data from dbo.Surveys and dbo.Options — Fuzzy
– Fuzzy, Commented Mar 23, 2016 at 20:48
Will it be Ok if You get result as comma separated nvarchar string not an array ? If so - google for "for xml path('')" — DimaSUN
– DimaSUN, Commented Mar 23, 2016 at 20:54
I added some sample data, if you need some more, don't hesitate to ask! To get the options inside a string would be only a last option since I need to fit the data into a specific defined data-type using an array or list... — Michael Heribert
– Michael Heribert, Commented Mar 23, 2016 at 21:16
do you store the data in your tables as JSON or are they in seperate columns as in surveys table has three columns id, thema, question — Fuzzy
– Fuzzy, Commented Mar 23, 2016 at 21:22
No, they are in seperate columns. I only used the json files because it appeared to be clearer than just copying the table. — Michael Heribert
– Michael Heribert, Commented Mar 23, 2016 at 21:24

Fuzzy · Accepted Answer · 2016-03-23 22:31:33Z

1

Based on the comments looks like you need to do this (some parts were sourced from here):

SAMPLE DATA:

IF OBJECT_ID('tempdb..#Surveys') IS NOT NULL
    DROP TABLE #Surveys;

CREATE TABLE #Surveys(id       UNIQUEIDENTIFIER
                , thema    VARCHAR(100)
                , question VARCHAR(250));

INSERT INTO #Surveys
VALUES
      ('3f07153f-78cf-4b03-a442-5dbbbdc6c85d'
     , 'Internet connection'
     , 'How fast is you connection?'),
          ('3f07153f-78cf-4b03-a442-5dbbbdc6c85c'
     , 'Internet connection'
     , 'How fast was your old connection?');

IF OBJECT_ID('tempdb..#Option') IS NOT NULL
    DROP TABLE #Option;

CREATE TABLE #Option(Id       UNIQUEIDENTIFIER
                , SurveyId UNIQUEIDENTIFIER
                , Answer   VARCHAR(250));

INSERT INTO #Option
VALUES
      ('3b3f9583-7d09-49d2-baee-d724c6ce3d9d'
     , '3f07153f-78cf-4b03-a442-5dbbbdc6c85d'
     , '10mbit/s'),
      ('55347f11-c01f-4b5f-86a3-9d9c66c2aef5'
     , '3f07153f-78cf-4b03-a442-5dbbbdc6c85d'
     , '20mbit/s'),
      ('55347f11-c01f-4b5f-86a3-9d9c66c2aef5'
     , '3f07153f-78cf-4b03-a442-5dbbbdc6c85c'
     , '5mbit/s'),
      ('55347f11-c01f-4b5f-86a3-9d9c66c2aef5'
     , '3f07153f-78cf-4b03-a442-5dbbbdc6c85c'
     , '10mbit/s');

QUERY:

    IF OBJECT_ID('tempdb..#tempRes') IS NOT NULL
    DROP TABLE #tempRes;
  SELECT A.id
            , A.thema
            , A.question
            , [Options] = CAST(B.Id AS VARCHAR(36))+', '+B.Answer 
            INTO #tempRes
        FROM   #Surveys AS A
             JOIN #Option AS B ON B.SurveyId = A.Id;

WITH Ranked ( id,thema,question,rnk, [Options] ) 
             AS ( SELECT id,thema,question,rnk = ROW_NUMBER() OVER( PARTITION BY id ORDER BY id ),
                         [Options]=CAST( [Options] AS VARCHAR(8000) ) FROM #tempRes ),
   AnchorRanked ( id,thema,question,rnk, [Options] ) 
             AS ( SELECT id,thema,question,rnk, [Options]
                    FROM Ranked
                   WHERE rnk = 1 ),
RecurRanked ( id,thema,question,rnk, [Options])
             AS ( SELECT id,thema,question,rnk, [Options]
                    FROM AnchorRanked
                   UNION ALL
                  SELECT Ranked.id, Ranked.thema,Ranked.question, Ranked.rnk,
                         RecurRanked.[Options] + '|' + Ranked.[Options]
                    FROM Ranked
                   INNER JOIN RecurRanked
                      ON Ranked.id = RecurRanked.id
                     AND Ranked.rnk = RecurRanked.rnk + 1 )
SELECT id,thema,question, MAX( [Options] )
      FROM RecurRanked
  GROUP BY id,thema,question;

RESULT:

the result is a pipe delimited column

edited Mar 23, 2016 at 22:31

answered Mar 23, 2016 at 21:39

Fuzzy

3,8102 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Michael Heribert Over a year ago

Thank you, that looks good! I will try to implement it as soon as possible! :)

Michael Heribert Over a year ago

That solution worked out just fine, but I still get two responses for the same survey. Is there a possibility of getting only one response that includes an array or list of those option-values like in the example I gave above?

Lucero Over a year ago

@MichaelHeribert This, or with two resultsets, is how it is done properly as relational data. However, you could have the options serialized into an XML column which can be used to transmit array-like data as part of a column without fancy hacks (such as CLR UDTs).

Fuzzy Over a year ago

@MichaelHeribert I am trying to work out how we can do this but Lucero hit the nail on the head there is no simple way of doing this.

Lucero Over a year ago

@MichaelHeribert You're welcome. Note that SQL Server 2016 intoroduces JSON capabilities similar to what we have for XML (see msdn.microsoft.com/en-US/library/dn921897.aspx for details), but since it is not yet widely deployed I was not able to try it for myself yet.

|

Fuzzy · Accepted Answer · 2016-03-23 22:37:15Z

1

Building on Kamran Farzami's solution, you can use an XML query to stuff the options into a single column on a single row.

SELECT A.id, A.thema, A.question, (
        SELECT * 
        FROM [Option] B 
        WHERE B.SurveyId = A.Id 
        FOR XML PATH('option'), ROOT('options'), TYPE
    ) AS options
    FROM Surveys AS A

http://sqlfiddle.com/#!6/0978e/3/0

edited Mar 23, 2016 at 22:37

Fuzzy

3,8102 gold badges17 silver badges34 bronze badges

answered Mar 23, 2016 at 22:10

Lucero

60.4k9 gold badges127 silver badges154 bronze badges

Collectives™ on Stack Overflow

SQL - Array as part of selected data

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related