Как создать запрос к существующей таблице и построить таблицу (представление) с агрегированными данными и ограничением?

Мне нравится ответ PIVOT, но вот еще один способ:

select VehicleNo,
max(DriverConsoleVersion) DriverConsoleVersion,
max(case when DevicePosition = 1 then DeviceSwVersion end) Device1SwVersion,
max(case when DevicePosition = 2 then DeviceSwVersion end) Device2SwVersion,
max(case when DevicePosition = 3 then DeviceSwVersion end) Device3SwVersion
from @DeviceInfo
group by VehicleNo
order by VehicleNo

Вы также можете сделать кастинг или форматирование на них. Таким образом, может быть:

       select ...,
       isnull(cast(cast(
          max(case when DevicePosition = 1 then DeviceSwVersion end)
            as decimal(8,2)) / 100) as varchar(5)), '')  Device1SwVersion,

С информацией, которая у нас есть на данный момент, я думаю, вам нужен запрос с PIVOT:

SELECT P.VehicleNo, V.DriverConsoleVersion, P.[1] AS [Device1SwVersion], P.[2] AS [Device1SwVersion], P.[3] AS [Device1SwVersion]
FROM (
    SELECT VehicleNo, [1], [2], [3]
    FROM (
        SELECT VehicleNo, DevicePosition, DeviceSwVersion
        FROM @DeviceInfo
    ) as d
    PIVOT (
        MAX(DeviceSwVersion)
        FOR DevicePosition IN ([1], [2], [3])
    ) PIV
) P 
LEFT JOIN @DeviceInfo V
    ON V.VehicleNo = P.VehicleNo AND V.DevicePosition = 1;

Вы можете создать представление с таким запросом.

Первый подзапрос получает 4 столбца для устройств от 1 до 3 для каждого транспортного средства. Затем он LEFT JOIN с таблицей SwVersion, чтобы получить версию консоли, связанную с устройством 1.

Вывод:

VehicleNo   DriverConsoleVersion    Device1SwVersion    Device1SwVersion    Device1SwVersion
102         1.34                    8.01                NULL                8.00
305         1.34                    8.01                8.00                8.00
312         1.33                    8.00                8.00                8.00
414         NULL                    NULL                8.01                8.00

Ваши данные:

Declare @DeviceInfo TABLE([DeviceSerial] int, [VehicleNo] int, [DevicePosition] int, [DeviceSwVersion] varchar(10), [DriverConsoleVersion] varchar(10));

INSERT INTO @DeviceInfo([DeviceSerial], [VehicleNo], [DevicePosition], [DeviceSwVersion], [DriverConsoleVersion])
VALUES
    (1040, 305, 3, '8.00', '0'),
    (1044, 305, 2, '8.00', '0'),
    (1063, 305, 1, '8.01', '1.34'),
    (1071, 312, 2, '8.00', '0'),
    (1075, 312, 1, '8.00', '1.33'),
    (1078, 312, 3, '8.00', '0'),
    (1099, 414, 3, '8.00', '0'),
    (1106, 414, 2, '8.01', '0'),
    (1113, 102, 1, '8.01', '1.34'),
    (1126, 102, 3, '8.00', '0')
;