Help with Query [closed]

Question

Help with Query [closed]

Navigation

#1 by (2 votes)

-4

I have the following SELECT in SQL Server

SELECT  FTags.DateAndTime, TAGS.TagName, TAGS.TagIndex, FTags.Val 
FROM TagTablePoços TAGS INNER JOIN FloatTablePoços FTags ON FTags.TagIndex = TAGS.TagIndex 
WHERE TAGS.TagName = '[AGUA]FT[1].Out' OR TAGS.TagName = '[AGUA]LT[1].Out'

It returns eight values, but I need four values, that is, create columns for the returned values to be displayed in 4 values.

sql sql-server

asked by anonymous 14.03.2018 / 14:20

1 answer

Method is running 3 times Recursive SQL using CTE

score 2 · Answer 1

If you only want 4 rows, because they are only 4 dates, then you should start select by dates, not by tagindex . And then you will want to show each value of tagname in columns, correct?

Considering the data:

create table tagtablepocos
(
    TagName varchar(20),
    tagindex int
);

 create table floattablepocos
 (
    dateandtime datetime,
   val float,
   tagindex int
 );


 insert into tagtablepocos values ('[AGUA]FT[1].Out',1);
 insert into tagtablepocos values ('[AGUA]LT[1].Out',5);

 insert into floattablepocos values ('2018-03-12 10:52:03', 66,1);
 insert into floattablepocos values ('2018-03-12 10:52:03', 8,5);

 insert into floattablepocos values ('2018-03-12 10:52:08', 23,1);
 insert into floattablepocos values ('2018-03-12 10:52:08', 47.99,5);

 insert into floattablepocos values ('2018-03-12 10:52:32', 5,1);
 insert into floattablepocos values ('2018-03-12 10:52:32', 47.99,5);

 insert into floattablepocos values ('2018-03-12 10:52:45', 0,1);
 insert into floattablepocos values ('2018-03-12 10:52:45', 47.99,5);

You can work with sub-select for each column you want:

Select distinct
    FTags.DateAndTime, 
    (select x.val from FloatTablePocos x where x.dateandtime = FTags.DateAndTime and x.TagIndex = 1) as FT,
    (select x.val from FloatTablePocos x where x.dateandtime = FTags.DateAndTime and x.TagIndex = 5) as LT
from FloatTablePocos FTags;

Result:

Iputitin SQLFiddle