1

What my data looks like:

MyData

It's 5 data points made at the same time. I want to combine all five into one row.

I tried using the following query:

SELECT 
    DateAndTime as Time, 
    (SELECT Val WHERE TagIndex = 0) as Normalized_Vac,
    (SELECT Val WHERE TagIndex = 1) as Avgerage_Vac,
    (SELECT Val WHERE TagIndex = 2) as RAL_ACT,
    (SELECT Val WHERE TagIndex = 3) as RAL_CMD,
    (SELECT Val WHERE TagIndex = 4) as PPH_weight
FROM 
    Line_32_floats
GROUP BY
    DateAndTime;

but I get the error:

Column 'Line_32_floats.TagIndex' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dylan Wright
  • 13
  • 2

3 Answers3

2

I would suggest conditional aggregation:

SELECT DateAndTime as Time, 
       MAX(CASE WHEN TagIndex = 0 THEN Val END) as Normalized_Vac,
       MAX(CASE WHEN TagIndex = 1 THEN Val END) as Avgerage_Vac,
       MAX(CASE WHEN TagIndex = 2 THEN Val END) as RAL_ACT,
       MAX(CASE WHEN TagIndex = 3 THEN Val END) as RAL_CMD,
       MAX(CASE WHEN TagIndex = 4 THEN Val END) as PPH_weight
FROM Line_32_floats f 
GROUP BY DateAndTime;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

You need conditional aggregation :

SELECT DateAndTime, 
      MAX(CASE WHEN TagIndex = 0 THEN Val END),
       . . . 
FROM Line_32_floats
GROUP BY DateAndTime;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
0

Here is my example of how to do it with PIVOT:

SELECT DateandTime, [0], [1], [2], [3], [4]
FROM 
(SELECT DateandTime, TagIndex, Val 
 FROM Line_32_floats WHERE DateandTime = '2018-08-09') as source_table
PIVOT
(
  MAX(Val)
  FOR TagIndex IN ([0], [1], [2], [3], [4])
) as pivot_table

You can access a working SQL Fiddle here: http://sqlfiddle.com/#!18/2106c/3/0

Since you want in one row I am assuming you are also grouping the different values of column Val, which I considered as MAX for this case.

Kevin Kamonseki
  • 141
  • 1
  • 6