Column type List

Question

I worked a long time with Postgresql and now with SQL Server I have missed some features that made life much easier.
I have for example a table that one of your columns should be a list of strings, namely, a array of sweep (in the postgre it was all very simple):

CREATE TABLE MyTable (
    id         integer PRIMARY KEY,
    name       varchar(30),
    questions  varchar(50)[]
);

Unfortunately in SQL Server this is not possible. I could make a second table called QuestionsForMyTable:

CREATE TABLE QuestionsForMyTable (
    id_of_mytable   integer,
    question        varchar(50)
);

And then do the proper relationship, but hey I fall into the disgrace of having to do one more JOIN.
This is really what I have left or is there a better way out?

Answer 1

"not to leave the question unanswered.."

Yes, if there is a list inside an object and it will be taken to the database, a second table must be created that will contain those items from the list; and the way to load the object complete will be through a join:

SELECT *
FROM MyTable
JOIN QuestionsForMyTable ON id = id_of_mytable

According to devmedia definition, "by means of this clause, the data of one table is used to select the data belonging to the other table".

Answer 2

Good afternoon,

This can be done using a Computed Column that returns the values of another table. It can be done as described below.

Create and popular table with elements to power the computed column:

CREATE TABLE QuestionsForMyTable 
(
    id_of_mytable   integer,
    question        varchar(50)
)

INSERT INTO QuestionsForMyTable 
    SELECT 1, 'Question 1' UNION
    SELECT 1, 'Question 2'

Create a function that returns the elements of this table to a given Id:

CREATE FUNCTION dbo.GetValue(@id int)
RETURNS nvarchar(max)
AS
BEGIN

    DECLARE @ret nvarchar(max)
    SET @ret = ''

    SELECT  @ret = @ret + CHAR(13)+CHAR(10) + question
    FROM    QuestionsForMyTable
    WHERE   id_of_mytable = @id

    RETURN @ret

END

Create table with computed column based on this function:

CREATE TABLE MyTable
(
    id         integer PRIMARY KEY,
    name       varchar(30),
    questions  as dbo.GetValue(id)
)

INSERT INTO MyTable
    SELECT 1, 'First set of questions' UNION
    SELECT 2, 'Second set of questions'

In the end, just select to Mytable to get the expected result.

SELECT * FROM MyTable