Search code examples
sqlsql-serversql-server-2012qsqlquery

How to create a query on an existing table and build a table(view) with aggregated data and a restriction?

What I have is an MS-SQL database that I use to store data/info coming from equipment that is mounted in some vehicles (1-3 devices per vehicle).

For the moment, there is a table in the database named DeviceStatus - a big table used to store every information from the equipment when they connect to the TCP-server. Records are added (sql INSERT) or updated (sql UPDATE) here.

The table looks like this:
enter image description here

Sample data:   
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

Remark: The driver console is always related to the device installed on first position (it's an extension of Device on Position 1; obvioulsly there's only one console per vehicle) - so, this will be some sort of restriction in order to have the correct info in the desired table(view) presented below :).

What I need is a SQL query (command/statement) to create a table(view) for a so-called "Software Versions Table", where I can see the software version for all devices installed in vehicles (all that did connect and communicate with the server)... something like the table below:
enter image description here

Remark: Device#1 for 414 is missing because it didn't communicate (not yet I guess...)

Solution

  • I like the PIVOT answer, but here is another way:

    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

    You can also do casting or formatting on them. So one might be:

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