List column

Question

List column

Navigation

#1 by (1 votes)
#2 by (1 votes)

2

I worked with PostgreSQL for a long time and now with SQL Server I have been missing some of the features that made life much easier. I have for example a table that one of its columns should be a list of strings , that is, an array of varchar (in 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 the relationship is due, but hey I fall into the misery of having to do another JOIN. Is this really what I have left, or is there a better way out?

sql-server

asked by anonymous 18.08.2018 / 23:06

2 answers

What is the difference between "string_view" for "string" and which one should you prefer? Recursion Doubt with String in Java

score 1 · Answer 1

"not to leave the question unanswered .."

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

SELECT *
FROM MyTable
JOIN QuestionsForMyTable ON id = id_of_mytable

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

score 1 · Answer 2

Good afternoon,

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

Create and populate the table with the elements to feed 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 for 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 the table with the 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'

At the end, just select a Mytable to get the expected result.

SELECT * FROM MyTable