add categories to a trend table

Question

Hi folks I've been noddling how to approach this one for a while now and I'm just stuck. Hoping this question is useful to the community.

I have a trend table with data like the first table below. I have another table with categories like the second table below. The goal is to display the data in a stacked column chart. Each column in the chart would be a last sample for that day, the series group for each column would be the circuit categories.

the data is sampled from every 10 minutes but for example sake I just entered 2 samples for each day:

time_stamp | circuit1 | circuit2 | circuit3

1/5/13 08:00 | 50 | 60 | 30

1/5/13 04:00 | 48 | 55 | 26

1/4/13 08:00 | 42 | 52 | 22

1/4/13 04:00 | 40 | 51 | 20

etc.

I have a category table similar to this:

Circuit_name | circuit_category

circuit1 | category4

circuit2 | category2

circuit3 | category12

etc.

Maybe I'm not thinking of a simpler way to do this from a reporting standpoint, but in order to get a stacked bar chart day by day like the requirements, I think I need a query which results in the following:

time_stamp | Circuit_name | Circuit_category | Value

1/5/13 08:00 | Circuit1 | category4 | 50

1/5/13 08:00 | Circuit2 | category2 | 60

1/5/13 08:00 | Circuit3 | category12 | 30

1/4/13 08:00 | Circuit1 | category4 | 42

1/4/13 08:00 | Circuit2 | category2 | 52

1/4/13 08:00 | Circuit3 | category12 | 22

I'm thinking I need to write a query to grab the max(time_stamp) grouped by day, but pivot the results so I can join the data to the category table. I've played around with using pivot on the first table since I have to join the circuit_name in table2 to the actual column names in table1, but I keep running into dead ends because I don't understand pivot well enough.

Anyway I'm willing to abandon table 2 if hard coding the circuit categories into the query is necessary, but again this is where I'm stuck. Any guidance would be appreciated.

The data is on a sql2008r2 server.

Thanks!

Answer 1

This seems like unpivot columns to rows... SQL Server has this function :) I believe following query can be improved and optimized. Pleaes comment after you have tried.

• SQLFIDDLE DEMO

Query:

select m.*, t.cat
from 
(SELECT ts, name, value
FROM
(
  SELECT ts,
    CONVERT(varchar(20), C1) AS c1,
    CONVERT(varchar(20), C2) AS c2,
    CONVERT(varchar(20), C3) AS c3
  FROM t2
) MyTable
UNPIVOT
(Value FOR name IN 
 (c1,c2,c3))AS MyUnPivot) m
left join t1 t
on t.name = m.name
;

Results:

TS                              NAME    VALUE   CAT
January, 05 2013 08:00:00+0000  c1      50      category4
January, 05 2013 08:00:00+0000  c2      60      category2
January, 05 2013 08:00:00+0000  c3      30      category12
January, 05 2013 04:00:00+0000  c1      48      category4
January, 05 2013 04:00:00+0000  c2      55      category2
January, 05 2013 04:00:00+0000  c3      26      category12
January, 04 2013 08:00:00+0000  c1      42      category4
January, 04 2013 08:00:00+0000  c2      52      category2
January, 04 2013 08:00:00+0000  c3      22      category12
January, 04 2013 04:00:00+0000  c1      40      category4
January, 04 2013 04:00:00+0000  c2      51      category2
January, 04 2013 04:00:00+0000  c3      20      category12