CREATE TABLE MyClass
(
Id INTEGER
,Class1 DECIMAL(10,5)
,class2 DECIMAL(10,5)
,class3 DECIMAL(10,5)
)
\\
INSERT INTO MyClass VALUES(123,0.002,0.001,0.002)
\\
CREATE TABLE classes
(
Id INT
,class1 DECIMAL(10,3)
)
\\
INSERT INTO classes VALUES (123,.001)\\
INSERT INTO classes VALUES (123,.002)\\
SOLUTIONWITH CTE
AS
(
SELECT Id,Class1,Class2,Class3,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Id) rnk FROM MyClass
)
,CTE1 AS
(
SELECT Id,Class FROM CTE
UNPIVOT
(
class
FOR r
IN (Class1, Class2, Class3)
)
)
SELECT * FROM CTE1 c INNER JOIN classes c1
ON c.Class = c1.class1
\\
OUTPUT ID CLASS ID CLASS1
1 123 0,002 123 0,002
2 123 0,001 123 0,001
3 123 0,002 123 0,002
Table creation and data generation
Open in new window
SOLUTION
Open in new window
OUTPUT
Open in new window