Search code examples
sqlsql-serverview

Perform calculation without having to do it manually for each column?

I have the following view set up in SQL Server:

VIEW

(left table: population data per year; middle table: municipalities; right table: municipality areas in km²)

Query

SELECT        
    dbo.T_GEMEINDE.GKZ, dbo.T_GEMEINDE.NAME, 
    dbo.T_BASE_DAUERSIEDLUNGSRAUM_GEMEINDE.FLAECHE_KM2 / dbo.T_BASE_DAUERSIEDLUNGSRAUM_GEMEINDE.DAUERSIEDLUNGSRAUM_KM2 AS [ges. Fläche / Dauersiedlungsr.], 
    dbo.T_BASE_GEMEINDE_BEVOELKERUNG_JAHR_BEGINN.J2017 / dbo.T_BASE_DAUERSIEDLUNGSRAUM_GEMEINDE.FLAECHE_KM2 AS [ges. Bevölkerungsdichte], 
    dbo.T_BASE_GEMEINDE_BEVOELKERUNG_JAHR_BEGINN.J2017 / dbo.T_BASE_DAUERSIEDLUNGSRAUM_GEMEINDE.DAUERSIEDLUNGSRAUM_KM2 AS [Bevölkerungsdichte Dauersiedlungsraum]
FROM
    dbo.T_BASE_DAUERSIEDLUNGSRAUM_GEMEINDE 
INNER JOIN
    dbo.T_GEMEINDE ON dbo.T_BASE_DAUERSIEDLUNGSRAUM_GEMEINDE.GKZ = dbo.T_GEMEINDE.GKZ 
INNER JOIN
    dbo.T_BASE_GEMEINDE_BEVOELKERUNG_JAHR_BEGINN ON dbo.T_GEMEINDE.GKZ = dbo.T_BASE_GEMEINDE_BEVOELKERUNG_JAHR_BEGINN.GKZ

The last column in the view contains a calculation (population density for 132 municipalities for a certain year) for the year 2017 and uses the column J2017 from the table seen on the left. This is the output (Bevölkerungsdichte Dauersiedlungsraum):

Current output:

OUTPUT

Desired output:

The rightmost column (Bevölkerungsdichte Dauersiedlungsraum) seen in the provided output screenshot has the output data of the calculation for the year 2017. The same output has to be generated for all the other years, but each as a separate column.

Question: how do I perform the calculation which you can see in the last column in the view for all years (J2017-J2050) without having to do it manually for each year column?

Thanks in advance.

Solution

  • if you want someone to provide you with a complete solution then you will need to supply:

    1. CREATE TABLE statements for the 3 tables
    2. INSERT INTO... statements to provide sample data for all 3 tables

    However, if you just want a suggestion about how to approach this problem then I would use an UNPIVOT statement to create a view/table that

    • holds all the columns in dbo.T_BASE_GEMEINDE_BEVOELKERUNG_JAHR_BEGINN apart from the "year" columns (J2017, J2018, j2019, ...)
    • adds a single "year" column with values from 2017 to 2050
    • adds a single value column to hold the population for each year

    By joining your existing tables to this new table/view and grouping by your new "year" column you should achieve what you want