0

I am trying to collate several tables into a single row display for reporting purposes - all on a date time value. I do not have an issue when joining disparate tables on say a CTE of datetime values. When I encounter a table with a different FK I get too many records for this to work. Example

Reporting Structure

DateTime       EngineName    EngineValue  PartName PartValue  Part2Name Part2Value
20160118 00:00 Engine1       100          Part1    100        Part2      200

Engine Table

DateTime               EngineName EngineValue
20160118 00:00         Engine1    100

Part Table

DateTime               Name(fK)     Value
20160118 00:00         Part1        100
20160118 00:00         Part2        200

At this point I have tried to create a CTE of datetimes and join the logs to the CTE on the datetime. I can't get this work and I know I'm not the first to create reports like this.

dd_va
  • 1
  • 3
  • Are there only 2 parts or is that dynamic in number? It seems you're trying to create a pivot table of which there are several examples on SO. Here's one such example for a dynamci number of values in the pivot. http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query If you only have 2 parts in question then you can avoid the dynamic portion of this SQL. – xQbert Jan 18 '16 at 21:29
  • Are the # of parts per engine needing to be reported always 2? always the same "part name" with different values? or dynamic in nature? (Different parts, different # of parts etc?) – xQbert Jan 18 '16 at 21:37

1 Answers1

0

If we assume Part1 and Part2 are actual names and static in number then you can do something like this: 

SELECT E.DateTime, E.EngineName, E.EngineValue,  P1.PartName, P1.PartValue,  P2.Part2Name, P2.Part2Value
FROM Engine E
INNER JOIN Part P1
 on E.DateTime = P1.DateTime
and P1.Name = 'Part1'
INNER JOIN Part P2
 on E.DateTime = P1.DateTime
and P2.Name = 'Part2'

If they are more dynamic in nature then Dynamic SQL will be needed.

If there's always 2 you could use an analytic such as row_number() over (partition by datetime order by name) as RN and join on row 1 and 2 instead of part names. i.e. instead of P2.Name ='part2' use P2.RN = 2 but this limits it to always be only 2 parts, we ignore all others.

So the real question is:

Are the # of parts per engine needing to be reported always 2? always the same "part name" with different values? or dynamic in nature?

xQbert
  • 34,733
  • 2
  • 41
  • 62
  • The real answer is the everything is relatively dynamic - even the "engine" table could have a number of engines. Basically I have log files from a system where every table has a datetime and the number of elements within the table can vary. – dd_va Jan 18 '16 at 22:23
  • In that case follow the link for how to create dynamic SQL for a Pivot table in my original comment to your question. – xQbert Jan 19 '16 at 14:35