I know that several discussions are being discussed here, but I could not find the discussions that cover my problem. So far I have received the following:
SELECT Perc, Salary FROM ( SELECT jobid, Salary_10 AS Perc10, Salary_25 AS Perc25, [Salary_Median] AS Median FROM vCalculatedView WHERE JobID = '1' GROUP BY JobID, SourceID, Salary_10, Salary_25, [Salary_Median] ) a UNPIVOT ( Salary FOR Perc IN (Perc10, Perc25, Median) ) AS calc1
Now I would like to add some other columns, for example. one of which is called Bonus, which I also want to add to Perc10, Perc25 and Median Rows.
As an alternative, I also made a request with a cross, but here it seems that you cannot “force” to sort the lines as you can with univot. In other words, I cannot have a custom view, but only a sort that matches the number inside the table, if I am right? At least here I get the result as I would like, but the lines are in the wrong order, and I don't have line names like Perc10 etc., which would be nice.
SELECT crossapplied.Salary, crossapplied.Bonus FROM vCalculatedView v CROSS APPLY ( VALUES (Salary_10, Bonus_10) , (Salary_25, Bonus_25) , (Salary_Median, Bonus_Median) ) crossapplied (Salary, Bonus) WHERE JobID = '1' GROUP BY crossapplied.Salary, crossapplied.Bonus
Perc means Percentile here.
The output should look something like this:
+--------------+---------+-------+ | Calculation | Salary | Bonus | +--------------+---------+-------+ | Perc10 | 25 | 5 | | Perc25 | 35 | 10 | | Median | 27 | 8 | +--------------+---------+-------+
Am I missing something or is something wrong? I am using MSSQL 2014, the output goes to SSRS. Thanks so much for any hint in advance!
Edit for clarification:. The Unpivot method gives the following result:
+--------------+---------+ | Calculation | Salary | +--------------+---------+ | Perc10 | 25 | | Perc25 | 35 | | Median | 27 | +--------------+---------+
therefore, there is no “Bonus” column.
The Cross-Apply-Method gives the following result:
+---------+-------+ | Salary | Bonus | +---------+-------+ | 35 | 10 | | 25 | 5 | | 27 | 8 | +---------+-------+
So, if you compare it with the expected output, you will notice that the column “Calculation” is absent, and the sorting of the rows is incorrect (note that row 25 | 5 is in the second row instead of the first).
Edit 2: view definition and sample data: The view basically just adds the computed table columns. In the table, I have Columns like Salary and Bonus for each JobID. Then View simply calculates percentiles like this:
Select Percentile_Cont(0.1) within group (order by Salary) over (partition by jobID) as Salary_10, Percentile_Cont(0.25) within group (order by Salary) over (partition by jobID) as Salary_25 from Tabelle
So, the result looks like this:
+----+-------+---------+-----------+-----------+ | ID | JobID | Salary | Salary_10 | Salary_25 | +----+-------+---------+-----------+-----------+ | 1 | 1 | 100 | 60 | 70 | | 2 | 1 | 100 | 60 | 70 | | 3 | 2 | 150 | 88 | 130 | | 4 | 3 | 70 | 40 | 55 | +----+-------+---------+-----------+-----------+
In the end, the view will be parameterized in the stored procedure.