Solved

query syntax

Posted on 2014-10-22
4
121 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
4 Comments
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 100 total points
Comment Utility
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 40

Accepted Solution

by:
Sharath earned 300 total points
Comment Utility
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 32

Assisted Solution

by:Stefan Hoffmann
Stefan Hoffmann earned 100 total points
Comment Utility
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
Comment Utility
interesting solution from ste5an, but we chose sharath as it was easiest to understand and maintain.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now