Derive a column value in my view based on related rows within the data

I have created a view, based on a set of data with truck axle/tire configurations, that expresses information about the truck configuration, axles, and positions (that is, tire positions).

SITE	TRUCK	AXLE_COUNT_ON_TRUCK	POSNUM	POS_SYNONYM	AXLE	POS_COUNT_ON_AXLE	EXPECTED_SCHEMA
CALGARY	HAUL1	2	1	LF	1	2	2x4
CALGARY	HAUL1	2	2	RF	1	2	2x4
CALGARY	HAUL1	2	3	LRO	2	4	2x4
CALGARY	HAUL1	2	4	LRI	2	4	2x4
CALGARY	HAUL1	2	5	RRI	2	4	2x4
CALGARY	HAUL1	2	6	RRO	2	4	2x4

Each row in the data is a tire position. And several rows in the data relate to each truck configuration. I am trying to derive, for each tire position, the parent truck's overall "schema". That is, is the truck a 2x2x2 with 3 axles and 2 tires on each? Or is it a 2x4, with 2 axles, 2 tires on the front and 4 on the rear? Or is it a 14-tire straddle carrier, 2x4x4x4, with 2 tires on the front, and 4 tires on the other 3 axles?

I am familiar with some windowing/analytic function syntax like RANK() OVER (PARTITION BY... ORDER BY...), and COUNT(DISTINCT xyz) OVER(...) but I just can't wrap my head around this.

The "Expected Schema" for the truck is the tire count for each axle (POS_COUNT_ON_AXLE), with 'x' as a separator. Simplifying by disregarding the separator, I can't grok how to generate 24 for that 2x4 (rear dually) truck on each of its 6 rows in the data.

The end goal is that I should be able to select from the view WHERE SITE='CALGARY' AND TRUCK='HAUL1' AND POSNUM=3 and see that the position synonym is 'LRO' and the truck's schema is '2x4'.

Fiddle: http://sqlfiddle.com/#!4/ef1a9/1

Solution

You can use the MIN and then the LISTAGG analytic functions:

SELECT SITE,
       TRUCK,
       AXLE_COUNT_ON_TRUCK,
       POSNUM,
       POS_SYNONYM,
       AXLE,
       POS_COUNT_ON_AXLE,
       EXPECTED_SCHEMA,
       LISTAGG(schema, 'x')
         WITHIN GROUP(ORDER BY axle)
         OVER (PARTITION BY site, truck) AS schema
FROM   (
  SELECT t.*,
         CASE
         WHEN posnum = MIN(posnum) OVER (PARTITION BY site, truck, axle)
         THEN pos_count_on_axle
         END AS schema
  FROM   Table1 t
)

Which, for the sample data, outputs:

SITE	TRUCK	AXLE_COUNT_ON_TRUCK	POSNUM	POS_SYNONYM	AXLE	POS_COUNT_ON_AXLE	EXPECTED_SCHEMA	SCHEMA
CALGARY	HAUL1	2	1	LF	1	2	2x4	2x4
CALGARY	HAUL1	2	2	RF	1	2	2x4	2x4
CALGARY	HAUL1	2	5	RRI	2	4	2x4	2x4
CALGARY	HAUL1	2	4	LRI	2	4	2x4	2x4
CALGARY	HAUL1	2	3	LRO	2	4	2x4	2x4
CALGARY	HAUL1	2	6	RRO	2	4	2x4	2x4
CALGARY	HAUL8	4	1	LF	1	2	2x4x4x4	2x4x4x4
CALGARY	HAUL8	4	2	RF	1	2	2x4x4x4	2x4x4x4
CALGARY	HAUL8	4	3	LMO	2	4	2x4x4x4	2x4x4x4
CALGARY	HAUL8	4	4	LMI	2	4	2x4x4x4	2x4x4x4
CALGARY	HAUL8	4	5	RMI	2	4	2x4x4x4	2x4x4x4
CALGARY	HAUL8	4	6	RMO	2	4	2x4x4x4	2x4x4x4
CALGARY	HAUL8	4	7	LMO	3	4	2x4x4x4	2x4x4x4
CALGARY	HAUL8	4	10	RMO	3	4	2x4x4x4	2x4x4x4
CALGARY	HAUL8	4	9	RMI	3	4	2x4x4x4	2x4x4x4
CALGARY	HAUL8	4	8	LMI	3	4	2x4x4x4	2x4x4x4
CALGARY	HAUL8	4	11	LRO	4	4	2x4x4x4	2x4x4x4
CALGARY	HAUL8	4	12	LRI	4	4	2x4x4x4	2x4x4x4
CALGARY	HAUL8	4	13	RRI	4	4	2x4x4x4	2x4x4x4
CALGARY	HAUL8	4	14	RRO	4	4	2x4x4x4	2x4x4x4
MIAMI	HAUL1	3	1	LF	1	2	2x2x2	2x2x2
MIAMI	HAUL1	3	2	RF	1	2	2x2x2	2x2x2
MIAMI	HAUL1	3	4	RM	2	2	2x2x2	2x2x2
MIAMI	HAUL1	3	3	LM	2	2	2x2x2	2x2x2
MIAMI	HAUL1	3	5	LR	3	2	2x2x2	2x2x2
MIAMI	HAUL1	3	6	RR	3	2	2x2x2	2x2x2

fiddle