TSQL Interleaved sequence without join operations or user defined objects

Is that possible to create an interleaved sequence with first and last values going in turns untill data set is empty without using joins and user defined functions or procedures?

  code  type    model   price
    1   Hats    1298    700,00
    1   Shoes   1232    600,00
    1   Pants   1276    400,00
    2   Hats    1321    970,00
    2   Shoes   1121    850,00
    2   Pants   1433    270,00
    3   Hats    1750    1200,00
    3   Shoes   1233    600,00
    3   Pants   1434    290,00
    4   Hats    1298    1050,00
    4   Shoes   1121    850,00
    4   Pants   1401    150,00
    5   Hats    1752    1150,00
    5   Shoes   1121    850,00
    5   Pants   1408    270,00
    6   Hats    1298    950,00
    6   Shoes   1233    950,00
    6   Pants   1288    400,00
    7   Shoes   1232    400,00
    8   Shoes   1232    350,00
    9   Shoes   1232    350,00
    10  Shoes   1260    350,00
    11  Shoes   1233    980,00
    12  Shoes   1233    970,00

I have added extra spaces between rows to get the interleaved sequence idea.

You want to get the odd values going from lowest coded items (asc) and even values with the highest coded items (desc). You also want to order type by Hats,Shoes and Pants.

code type   model   price
1   Hats    1298    700,00
1   Shoes   1232    600,00
1   Pants   1276    400,00

6   Hats    1298    950,00
12  Shoes   1233    970,00
6   Pants   1288    400,00

2   Hats    1321    970,00
2   Shoes   1121    850,00
2   Pants   1433    270,00

5   Hats    1752    1150,00
11  Shoes   1233    980,00
5   Pants   1408    270,00

3   Hats    1750    1200,00
3   Shoes   1233    600,00
3   Pants   1434    290,00

4   Hats    1298    1050,00
10  Shoes   1260    350,00
4   Pants   1401    150,00

4   Shoes   1121    850,00

9   Shoes   1232    350,00

5   Shoes   1121    850,00

8   Shoes   1232    350,00

6   Shoes   1233    950,00

7   Shoes   1232    400,00

Right now I came up with solution that includes joins but I am looking for something that would work without using it.

My solution with using joins:

with cteasc as 
(
    select
    ROW_NUMBER() over(order by code,charindex(type, 'HatsShoesPants'))id
    ,(ROW_NUMBER() over(partition by type order by code,charindex(type, 'HatsShoesPants')) + 1) / 2 offsetasc
    ,code,model,price,type 
    from mydata
),
ctedsc as 
(
    select
    ROW_NUMBER() over (partition by type order by code desc)id
    ,code,model,price,type
    from cteasc
)
select t1.id
,case
    when t1.code%2=1
    then LAG(t1.type,t1.code-t1.offsetasc,t1.type)over(partition by t1.type order by t1.id)
    else LAG(t2.type,t1.code-t1.offsetasc,t1.type)over(partition by t1.type order by t1.id)
end type
,case
    when t1.code%2=1
    then LAG(t1.model,t1.code-t1.offsetasc,t1.model)over(partition by t1.type order by t1.id)
    else LAG(t2.model,t1.code-t1.offsetasc,t1.model)over(partition by t1.type order by t1.id)
end model
,case
    when t1.code%2=1
    then LAG(t1.price,t1.code-t1.offsetasc,t1.price)over(partition by t1.type order by t1.id)
    else LAG(t2.price,t1.code-t1.offsetasc,t1.price)over(partition by t1.type order by t1.id)
end price 
from
cteasc t1
join ctedsc t2
on t1.code = t2.id
and t1.type = t2.type


Source: sql

Leave a Reply