Solved

query syntax

Posted on 2014-10-22
4
129 Views
Last Modified: 2014-10-22
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

0
Comment
Question by:soozh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 100 total points
ID: 40396358
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
 
LVL 41

Accepted Solution

by:
Sharath earned 300 total points
ID: 40396364
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
 
LVL 34

Assisted Solution

by:ste5an
ste5an earned 100 total points
ID: 40396383
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
 

Author Closing Comment

by:soozh
ID: 40396398
interesting solution from ste5an, but we chose sharath as it was easiest to understand and maintain.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question