sql server unpivoting table

Question

Hi, I'm having trouble trying to unpivot the following table.

| operation | ...other columns... | A1 | B1 | C1 | A2 | B2 | C2 | A3 | B3 | C3 | ... |

into something like this...

| operation | ...other columns... | AValue | BValue | CValue |

Right now im doing it like this

SELECT operation , ...other columns , Avalue, BValue , CValue
FROM (SELECT operation, ...other columns, A1, B1, C1
      FROM MyTable

      UNION ALL  

      SELECT operation, ...other columns, A2, B2, C2
      FROM MyTable

      UNION ALL

      SELECT operation, ...other columns, A3, B3, C3
      FROM MyTable
)

The problem is that i have 30 sets of ABCs, so 30 unions. I've tried using unpivot but i haven't being able to come up with the right solution, this is my first time trying to use it.

This is my attempt usign UNPIVOT

SELECT opration
       , ...other columns ...
       ,AValue
       ,BValue
       ,CValue
FROM     MyTable
UNPIVOT ( [AValue] FOR XX IN (A1,A2,A3,...,A30)) AS upv1
UNPIVOT ( [BValue] FOR yy IN (B1,B2,B3,...,B30)) AS upv2
UNPIVOT ( [CValue] FOR ZZ IN (C1,C2,C3,...,C30)) AS upv3

The problem is that i don't understand why is returning millions of rows, when the expected should be about 10k. Any ideas?

EDIT: I just realized that each UNPIVOT works with the result set of the previous unpivot statement which cause the 'data explosion'.

Thanks

Answer 1

I'm not too clear on what you're looking for but you might try "union" and not "union all".

Answer 2

Done! Plamen Ratchev gave me the final help here

SELECT opration
       , ...other columns ...
       ,AValue
       ,BValue
       ,CValue
FROM     MyTable
UNPIVOT ( [AValue] FOR XX IN (A1,A2,A3,...,A30)) AS upv1
UNPIVOT ( [BValue] FOR yy IN (B1,B2,B3,...,B30)) AS upv2
UNPIVOT ( [CValue] FOR ZZ IN (C1,C2,C3,...,C30)) AS upv3
WHERE RIGHT(XX, 2) = RIGHT(YY, 2)
  AND RIGHT(XX, 2) = RIGHT(ZZ, 2)