Using SQL Server I have two tables, below sample Table #T1 in DB has well over a million rows, Table #T2 has 100 rows. Both tables are in Column format and I need to Pivot to rows and join both.
Can I get it all in one query with Cross Apply and remove the cte?
This is my code, I have correct output but is this the most efficient way to do this considering number of rows?
with cte_sizes as ( select SizeRange,Size,ColumnPosition from #T2 cross apply ( values(Sz1,1),(Sz2,2),(Sz3,3),(Sz4,4) ) X (Size,ColumnPosition) ) select a.ProductID,a.SizeRange,c.Size,isnull(x.Qty,0) as Qty from #T1 a cross apply ( values(a.Sale1,1),(a.Sale2,2),(a.Sale3,3),(a.Sale4,4) ) X (Qty,ColumnPosition) inner join cte_sizes c on c.SizeRange = a.SizeRange and c.ColumnPosition = x.ColumnPosition
I have also code and considered this but is this the CROSS APPLY a better method?
with cte_sizes as ( select 1 as SizePos union all select SizePos + 1 as SizePos from cte_sizes where SizePos < 4 ) select a.ProductID ,a.SizeRange ,(case when b.SizePos = 1 then c.Sz1 when b.SizePos = 2 then c.Sz2 when b.SizePos = 3 then c.Sz3 when b.SizePos = 4 then c.Sz4 end ) as Size ,isnull((case when b.SizePos = 1 then a.Sale1 when b.SizePos = 2 then a.Sale2 when b.SizePos = 3 then a.Sale3 when b.SizePos = 4 then a.Sale4 end ),0) as Qty from #T1 a inner join #T2 c on c.SizeRange = a.SizeRange cross join cte_sizes b