SQL - Pivot or Unpivot?

Question

Another time, another problem. I have the following table:

|assemb.|Repl_1|Repl_2|Repl_3|Repl_4|Repl_5|Amount_1|Amount_2|Amount_3|Amount_4|Amount_5|
|---------------------------------------------------------------------------------------|
|4711001|111000|222000|333000|444000|555000|       1|       1|       1|       1|       1|
|---------------------------------------------------------------------------------------|
|4711002|222000|333000|444000|555000|666000|       1|       1|       1|       1|       1|
|---------------------------------------------------------------------------------------|

And here what I need:

|Article|Amount|
|--------------|
| 111000|     1|
|--------------|
| 222000|     2|
|--------------|
| 333000|     2|
|--------------| 
| 444000|     2|
|--------------| 
| 555000|     2|
|--------------|
| 666000|     1|
|---------------

Repl_1 to Repl_10 are replacement-articles of the assembly. I can have n assemblies with to 10 rep-articles. At the end I need to overview all articles with there amounts of all assemblies.

THX.

Best greetz Vegeta

Answer 1

This is probably the quickest way of achieving it using UNION ALL. However, I'd recommend normalising your table

SELECT Article, SUM(Amount) FROM (
    SELECT Repl_1 AS Article, SUM(Amount_1) AS Amount FROM #Test GROUP BY Repl_1
    UNION ALL
    SELECT Repl_2 AS Article, SUM(Amount_2) AS Amount FROM #Test GROUP BY Repl_2
    UNION ALL
    SELECT Repl_3 AS Article, SUM(Amount_3) AS Amount FROM #Test GROUP BY Repl_3
    UNION ALL
    SELECT Repl_4 AS Article, SUM(Amount_4) AS Amount FROM #Test GROUP BY Repl_4
    UNION ALL
    SELECT Repl_5 AS Article, SUM(Amount_5) AS Amount FROM #Test GROUP BY Repl_5
) tbl GROUP BY Article