Search code examples
sqlsql-serverunpivotlateral-join

How make from 4 variables only two in SQL?

I have the following table enter image description here

and want to achieve the following result

enter image description here

As a result, group1 and group 2 are grouped to GROUP, and quantity1/2 to QUANTITY. I tried with cross apply, but the table starts being too big and with duplicates.

Solution

  • In SQL Server, I would recommend cross apply to unpivot:

    select t.date, t.fund, x.*
from mytable t
cross apply (values (t.group1, t.quantity1), (t.group2, t.quantity2)) as x(grp, qty)

    Lateral joins are a powerful tool. This is more efficient than union all, because the table is scanned only once.