TSQL Multiple column unpivot with named rows possible?

Question

I know there are several unpivot / cross apply discussions here but I was not able to find any discussion that covers my problem. What I've got so far is 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, what I would like is to add several other columns, eg. one named Bonus which I also want to put in Perc10, Perc25 and Median Rows.

As an alternative, I also made a query with cross apply, but here, it seems as if you can not "force" sort the rows like you can with unpivot. In other words, I can not have a custom sort, but only a sort that is according to a number within the table, if I am correct? At least, here I do get the result like I wish to have, but the rows are in a wrong order and I do not have the rows 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 stands for Percentile here.

Output is intended to be something like this:

+--------------+---------+-------+
| Calculation  | Salary  | Bonus |
+--------------+---------+-------+
| Perc10       |      25 |     5 |
| Perc25       |      35 |    10 |
| Median       |      27 |     8 |
+--------------+---------+-------+

Do I miss something or did I something wrong? I'm using MSSQL 2014, output is going into SSRS. Thanks a lot for any hint in advance!

Edit for clarification: The Unpivot-Method gives the following output:

    +--------------+---------+
    | Calculation  | Salary  |
    +--------------+---------+
    | Perc10       |      25 |
    | Perc25       |      35 |
    | Median       |      27 |
    +--------------+---------+

so it lacks the column "Bonus" here.

The Cross-Apply-Method gives the following output:

+---------+-------+
| Salary  | Bonus |
+---------+-------+
|      35 |    10 |
|      25 |     5 |
|      27 |     8 |
+---------+-------+

So if you compare it to the intended output, you'll notice that the column "Calculation" is missing and the row sorting is wrong (note that the line 25 | 5 is in the second row instead of the first).

Edit 2: View's definition and sample data: The view basically just adds computed columns of the table. In the table, I've got Columns like Salary and Bonus for each JobID. The View then just computes the 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 output is like:

+----+-------+---------+-----------+-----------+
| 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 a stored procedure.

Maudemaudie

Your vCalculatedView seems to do the calculations in advance... Your group by isn't doing anything... Please show the result you're getting with your statement and provide some sample data and expected output. – Pottle

The group by in the Cross Apply code does do something: If I would not put it in, Perc10, Perc25, Median would repeat over and over. That is because a Job ID is not the employee ID and as such, a Job ID may contain more than one employee. – Maudemaudie

OK. I see what you mean. Might be, that a CTE with a SELECT DISTINCT is what you rather need. GROUP BY you'd use for aggregations like MAX() or SUM() Please provide the result of your current query and how your data should be displayed. – Pottle

Thank you for your reply! It is not clear to which query you're refering to here. Are you refering Select Distinct to the first option (Unpivot) or to the second (cross-apply)? As for the output, my small table at the end of my post shows what is intended and what unpivot gives - without the second column, though. With cross apply, i get the same result, but without the column "Calculation" and with all other columns (Salary, Bonus). However, the ouput here is not sorted, so eg. Perc25 is above Perc10 instead like above in the table shown. – Maudemaudie

Ah, I think i just realized what you meant: You were refering to the Cross Apply method and meant to have a With Clause to refer it to a name like T and use it with Select * From T Group by... which then leaves the question: how do I get the column "Calculation" with which I could do a Order BY and CASE ? – Maudemaudie

Would you please provide sample data as it comes out of your vCalculatedView? The VIEW's definition might help also. I do not understand up to which level the aggregation is done there... – Pottle

I just realized that I forgot a where JobID = 1 Statement above which I corrected just now. – Maudemaudie

Answer 1

Might this be your approach?

After your edits I understand, that your solution with CROSS APPLY would comes back with the right data, but not in the correct output. You can add constant values to your VALUES and do the sorting in a wrapper SELECT:

SELECT wrapped.Calculation,
       wrapped.Salary,
       wrapped.Bonus
FROM
(
    SELECT crossapplied.*
    FROM vCalculatedView v
    CROSS APPLY (
        VALUES
              (1,'Perc10',Salary_10, Bonus_10)
            , (2,'Perc25',Salary_25, Bonus_25)
            , (3,'Median',Salary_Median, Bonus_Median)
    ) crossapplied (SortOrder,Calculation,Salary, Bonus)
    WHERE JobID = '1'
    GROUP BY crossapplied.SortOrder,
             crossapplied.Calculation,
             crossapplied.Salary,
             crossapplied.Bonus
) AS wrapped
ORDER BY wrapped.SortOrder

Pottle

Thank you! I marked your solution as correct. However, for other readers, you need to put all columns (SortOrder, Calculation, Salary, Bonus) in to the GROUP BY Clause, not just Salary and Bonus. Last night, I wrote the query with the UNPIVOT Method with CTE and LEFT JOIN, but that is of course a much longer code. For the readers to come, I will put this in as another answer, but that is not to beat your solution. Your solutions shows once again that most of the time, CROSS APPLY beats Unpivot just like numerous bloggers show in comparisons. – Maudemaudie

Thx for the acceptance! I edited my answer to reflect your hint about GROUP BY COLUMNS. If you like my answer it would be kind to additionally vote it up. Voting and accepting are two separate steps... Thx again! – Pottle