2

I basically have a large database with columns [t_stamp],[floatvalue], and [tagpath] columns. I'm trying to eventually be able to have a query with only [t_stamp], multiple columns of [floatvalue] sorted by different [tagpath] values. I am not turning rows into columns. I am duplicating one column with different filtering. No pivoting involved, as far as I am aware. I'm really sorry if this is confusing so I'll be sure to add pictures of tables. I thought this would be a perfect example of a case scenario where each column can simple have a case filtering the tagpath but when I try to do this I get multiple results of [t_stamp] including a fairly slow sql query with only doing two cases. (will eventually need to do 15-20 but once confirmed time to query won't be a factor)

select distinct
[t_stamp] as 'Time',
(case when dbo.taghistoriancombined.tagpath like 'extruder 1/plc/temps/exhaust wet bulb temp' then [floatvalue] end) as 'Wet Bulb',
(case when dbo.taghistoriancombined.tagpath like 'extruder 1/plc/extruder/motor load' then [floatvalue] end) as 'Motor Load'
from [dbo].[TagHistorianCombined]

So then I thought, "well maybe I can just take two queries with null placeholders and use a union that will get rid of any possible overlay.

SELECT
[t_stamp] as time
,[floatvalue] as 'Motor Load'
,null as 'Wet Bulb'
FROM [dbo].[TagHistorianCombined]
where tagpath like 'extruder 1/plc/extruder/motor load'
 union
SELECT
[t_stamp] as time
,null as 'Motor Load'
,[floatvalue] as 'Wet Bulb'
FROM [dbo].[TagHistorianCombined]
where tagpath like 'extruder 1/plc/temps/exhaust wet bulb temp'
order by time desc

but unfortunately this gave me same result. Desired Schema but Undesired Results

What would be the best way to approach this? I have pretty rough explaining skills as is and this is my first post on this website so if I missed anything or if I need to explain further please don't hesitate to ask.

Jacerracer3
  • 23
  • 4
  • Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) – GSazheniuk Nov 30 '18 at 18:55
  • I could be wrong but I do feel pretty confident that this has nothing to do with pivoting tables. – Jacerracer3 Nov 30 '18 at 20:45

1 Answers1

0

I think you want group by. The following returns one row per timestamp:

select t_stamp,
       max(case when thc.tagpath like 'extruder 1/plc/temps/exhaust wet bulb temp' then floatvalue end) as WetBulb,
       max(case when thc.tagpath like 'extruder 1/plc/extruder/motor load' then floatvalue end) as MotorLoad
from [dbo].TagHistorianCombined thc
where thc.tagpath like 'extruder 1/plc/temps/exhaust wet bulb temp' or
      thc.tagpath like 'extruder 1/plc/extruder/motor load'
group by t_stamp
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786