Join two tables, group and pivot in SQL

Question

I have two tables where I would like to join on two fields, create groups based on certain values within the table, and perform a pivot. Here are my tables:

table 1

id  date        Total   avail   
123 9/20/2020   10      2   
133 9/20/2020   20      3   
144 9/20/2020   10      1   
55  9/20/2020   15      5   

table2

id  sku     date
123 Ax-89   9/20/2020
144 At-90   9/20/2020
133 By-25   9/20/2020
55  Bt-20   9/20/2020

Desired Result:

id  date    field   group   pivot   sku
123 9/20/2020   2   group1  avail   Ax-89
123 9/20/2020   8   group1  used    Ax-89
144 9/20/2020   1   group1  avail   At-90
144 9/20/2020   9   group1  used    At-90
133 9/20/2020   3   group2  avail   By-25
133 9/20/2020   7   group2  used    By-25
55  9/20/2020   5   group2  avail   Bt-20
55  9/20/2020   10  group2  used    Bt-20

What I am doing

SELECT table1.id table1.date, table1.total, table1.avail ,
       table2.id, table2.sku, table2.date
FROM   table1
JOIN   table2 ON
table1.date = table2.date
table1.id = table2.id
PIVOT(table1.avail) AS Pivot_table
GROUPBY table2.sku WHERE sku CONTAINS 'Ax', 'At' AS 'Group1' AND
WHERE sku CONTAINS 'By', 'Bt' AS 'Group2'

I am still researching, any suggestion is appreciated.

Answer 1

You want to unpivot. In SQL Server I would recommend cross apply:

select t1.id, t1.date, x.field, x.pivot, t2.sku
from table1 t1
inner join table2 t2 on t2.id = t1.id and t1.date = t2.date
cross apply (values (t1.avail, 'avail'), (t1.total - t1.avail, 'used')) as x(field, pivot)

It is not clear which logic you want for column group (which, by the way, is a language keyword, so not a good choice for a column name). Based on your attempt, it looks like you want to assign that based on the sku. If so, use a case expression:

select t1.id, t1.date, x.field, x.pivot, t2.sku,
    case 
        when t2.sku like 'Ax%' or t2.sku like 'At%' then 'group1'
        when t2.sku like 'By%' or t2.sku like 'Bt%' then 'group2'
    end as grp
from table1 t1
inner join table2 t2 on t2.id = t1.id and t1.date = t2.date
cross apply (values (t1.avail, 'avail'), (t1.total - t1.avail, 'used')) as x(field, pivot)