unpivot result from temporary table

Question

i try to get the result in an unpivot resultset but with more column I've never used this technique.

I got a temporary table.. with many field... but focus on listing below.

SELECT 
    u.IDNode_u,
    l.lev_u
from #Slugs
unpivot
(
  IDNode_u  for node       in (IDNodo, IDNodoPadre, [IDNodoPadre-2], [IDNodoPadre-3], [IDNodoPadre-4], [IDNodoPadre-5], [IDNodoPadre-6])
) u
unpivot
(
  lev_u   for level   in (LivelloTop, [LivelloTop-1], [LivelloTop-2], [LivelloTop-3], [LivelloTop-4], [LivelloTop-5], [LivelloTop-6])
) l

The query is ok only with the first unpivot, with second add I got error: "The multi-part identifier "u.IDNodo_u" could not be bound.", but I think is bound to the second unpivot.

Can someone with me some advise? Alen Italy.

Answer 1

I think the easiest way to unpivot is to use cross apply:

select u.IDNode_u, l.lev_u
from #slugs s cross apply
     (values (IDNodo), (IDNodoPadre), ([IDNodoPadre-2]), ([IDNodoPadre-3]),
             ([IDNodoPadre-4]), ([IDNodoPadre-5]), ([IDNodoPadre-6])
     ) u(IDNode_u) cross apply
     (values (LivelloTop), ([LivelloTop-1]), ([LivelloTop-2]),
             ([LivelloTop-3]), ([LivelloTop-4]), ([LivelloTop-5]),
             ([LivelloTop-6])
     ) l(lev_u);

EDIT:

I think you intend:

select ul.IDNode_u, ul.lev_u
from #slugs s cross apply
     (values (IDNodo, LivelloTop),
             (IDNodoPadre, [LivelloTop-1]),
             ([IDNodoPadre-2], [LivelloTop-2]),
             ([IDNodoPadre-3], [LivelloTop-3]),
             ([IDNodoPadre-4], [LivelloTop-4]),
             ([IDNodoPadre-5], [LivelloTop-5]),
             ([IDNodoPadre-6], [LivelloTop-6])
     ) ul(IDNode_u, lev_u) ;