query syntax

This is a simple question and i just need a bit of advise.

I have to produce the nvarchar(max) variable @series.  See below.

      set @series = '<series>[
                {name: "Vanföreställningar", data: [0], color: "STEELBLUE"},
                {name: "Hallucinationer", data: [0], color: "FIREBRICK"},
				{name: "Agitation/aggressivitet",data: [0], color: "GOLD"},
                {name: "Depression/dysfori", data: [0], color: "LIGHTBLUE"},
                {name: "Ångest", data: [0], color: "INDIANRED"},
                {name: "Eufori/upprymdhet", data: [0], color: "DIMGREY"},
                {name: "Apati/likgiltighet", data: [0], color: "MEDIUMBLUE"},
                {name: "Hämningslöshet", data: [0], color: "POWDERBLUE"},
                {name: "Irritabilitet/labilitet", data: [0], color: "GREY"},
                {name: "Motorisk av. beteende",data: [0], color: "MEDIUMVIOLETRED"},
                {name: "Sömn", data: [0], color: "GREEN"},
                {name: "Aptit/ätstörningar", data: [0], color: "SKYBLUE"}
                ]</series>'
		

Open in new window

           

Currently the value "data" is set to zero for each object in the series.

The values for each object is held in a table whose columns are named: Series1, Series2, Series3 etc.  (there are 12).

What would be the best way to populate @series using a select statement given that i know the id value of the record i am interested in.

i.e.
select series1, series2, series3 ... from MyTable where id = 12

Open in new window


Should i use a variables and a select statement along the lines

select @series1=series1, @series2=series2 ect..

and then build @series by using something like i show below, or is there a more elegant solution?

      set @series = '<series>[
                {name: "Vanföreställningar", data: [' + @series1 +'], color: "STEELBLUE"},
                {name: "Hallucinationer", data: [' + @series2 + '], color: "FIREBRICK"},

etc

               ]</series>'
		

Open in new window

soozhAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
You can try like this. I assume you have one record in your MyTable.
SELECT @series = '<series>[ {name: "Vanföreställningar", data: [' + series1 +'], color: "STEELBLUE"},                                    {name: "Hallucinationer", data: [' + series2 + '], color: "FIREBRICK"}, etc ]</series>'
  FROM MyTable

Open in new window

0
 
Lee SavidgeConnect With a Mentor Commented:
I can't think of a better way other than getting the data first in a series of 12 variables and then setting the @series variable.

declare @series1 nvarchar(100), -- or whatever size it needs to be
       @series2 nvarchar(100),
       @series3 nvarchar(100),
       @series4 nvarchar(100),
       @series5 nvarchar(100),
       @series6 nvarchar(100),
       @series7 nvarchar(100),
       @series8 nvarchar(100),
       @series9 nvarchar(100),
       @series10 nvarchar(100),
       @series11 nvarchar(100),
       @series12 nvarchar(100)

select @series1 = ..., @series2 = ..., etc.

select @series = '<series>[
                {name: "Vanföreställningar", data: [' + @series1 +'], color: "STEELBLUE"},
                {name: "Hallucinationer", data: [' + @series2 + '], color: "FIREBRICK"},
...
               ]</series>'

Open in new window

0
 
ste5anConnect With a Mentor Senior DeveloperCommented:
The values for each object is held in a table whose columns are named: Series1, Series2, Series3 etc.  (there are 12).

This is repeating group and should be avoided. I would choose an EAV model. e.g.

DECLARE @Entities TABLE
    (
      EntityID INT ,
      EntityName NVARCHAR(255)
    );

DECLARE @Attributes TABLE
    (
      AttributeID INT ,
      AttributeName NVARCHAR(255) ,
      AttributeColor NVARCHAR(255)
    );

DECLARE @Values TABLE
    (
      EntityID INT ,
      AttributeID INT ,
      Value INT
    );

INSERT  INTO @Entities
VALUES  ( 12, N'Series 12' );

INSERT  INTO @Attributes
VALUES  ( 1, 'Vanföreställningar', 'STEELBLUE' ),
        ( 2, 'Hallucinationer', 'FIREBRICK' ),
        ( 3, 'Agitation/aggressivitet', 'GOLD' ),
        ( 4, 'Depression/dysfori', 'LIGHTBLUE' ),
        ( 5, 'Ångest', 'INDIANRED' ),
        ( 6, 'Eufori/upprymdhet', 'DIMGREY' ),
        ( 7, 'Apati/likgiltighet', 'MEDIUMBLUE' ),
        ( 8, 'Hämningslöshet', 'POWDERBLUE' ),
        ( 9, 'Irritabilitet/labilitet', 'GREY' ),
        ( 10, 'Motorisk av. beteende', 'MEDIUMVIOLETRED' ),
        ( 11, 'Sömn', 'GREEN' ),
        ( 12, 'Aptit/ätstörningar', 'SKYBLUE' );

INSERT  INTO @Values
VALUES  ( 12, 1, 10 ),
        ( 12, 2, 20 ),
        ( 12, 3, 30 ),
        ( 12, 4, 40 ),
        ( 12, 5, 50 ),
        ( 12, 6, 60 ),
        ( 12, 7, 70 ),
        ( 12, 8, 60 ),
        ( 12, 9, 50 ),
        ( 12, 10, 40 ),
        ( 12, 11, 30 ),
        ( 12, 12, 20 );

DECLARE @JsonTemplate NVARCHAR(MAX) = N'{ name: "@Attribute", data: "@Value", color: "@Color" }';
DECLARE @Json NVARCHAR(MAX) = N'';

SELECT  @Json = @Json + ', ' + REPLACE(REPLACE(REPLACE(@JsonTemplate, '@Attribute', A.AttributeName), '@Color', A.AttributeColor), '@Value', V.Value)
FROM    @Attributes A
        INNER JOIN @Values V ON V.AttributeID = A.AttributeID
WHERE   V.EntityID = 12;

SELECT  '[' + STUFF(@Json, 1, 2, '') + ']';

Open in new window

0
 
soozhAuthor Commented:
interesting solution from ste5an, but we chose sharath as it was easiest to understand and maintain.
0
All Courses

From novice to tech pro — start learning today.