Transforming a sheet into a table with column names as values in SQL Server

I’ve been given the task of turning the following Excel table into a database table in SQL Server (I have shortened the row count, of course).

A car has to go to service every 10.000 kilometers, but for some models there is a fast service that applies only to certain mileages (I don’t know what mileage is called in kilometers lol).

The table shows car brands and models, and each following column represents the next maintenance service (i.e. column [10] represents the first service, performed at 10.000km, column [20] represents car service performed at 20.000km, etc.).

The values inside the mileage column will indicate if quick service “applies” to the corresponding model and mileage. (i.e. Quick service applies to [Changan A500] at 10.000km and 20.000km, but not at 30.000 or 40.000)

As mentioned before, I need to transform this table into a database table in SQL Server, with the following format.

In this format, there will be a row for every model and the mileage at which quick service corresponds. I hope this clarifies the requirement:

I can make a new SQL table with the source table, and then extract the data and insert it into the required table after transforming it (I assume there is no easy way of putting the information in the new format from the source Excel file).

Right now I’m thinking of using pointers in order to turn this data into a table, but I’m not very good at using pointers and I wanted to know if there might be an easier way before trying the hard way.

The point is to make this scalable, so they can keep adding models and their respective mileages.

How would you do it? Am I complicating myself too much by using pointers or is it a good idea?

Thanks, and sorry I used so many pictures, just thought it might clarify better, and the reason I haven’t uploaded any SQL is because I just can’t figure out yet how I plan to transform the data.

Answer

I’m not sure you can have a column named 10, 20, 30, 40, etc, but this is how I would solve this kind of problem.

SELECT *
INTO unpvt
FROM (VALUES
   ('chengan', 'a500', 'applies', 'applies', '', ''),
   ('renault', 'kwid', 'applies', 'applies', 'applies', 'applies')
   )
v (brand, model, ten, twenty, thirty, fourty)


Select * 
From unpvt


SELECT YT.brand,
       YT.model,
       V.number
FROM dbo.unpvt YT
     CROSS APPLY (VALUES(ten),
                        (twenty),
                        (thirty),
                        (fourty))
     V(number)

Result: