Solved

query syntax

Posted on 2014-10-22
4
128 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

734 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