在informatica中使用带有自连接的SQL查询

Question

input table has:

CODE    CATEGORY    COLOR
-1      3          Green
18578   2          Green
18578   3          Green
20032   1          Green
20032   3          Green
20032   2          Green

输出表应该有:

CODE    CATEGORY    COLOR
-1          3       Green
-1         100      Green
-1         200      Green
18578       3       Green
18578      100      Green
18578       2       Green
20032       1       Green
20032       3       Green
20032       2       Green

应该使用什么样的查询，使用了多种，但是

Answer 1

您可以预聚合原始表，然后有条件地取消pivot以获得额外的行

INSERT YourTable
(CODE, CATEGORY. COLOR)
SELECT
t.CODE,
v.CATEGORY,
'Green'
FROM (
SELECT
t.CODE,
cnt = COUNT(*)
FROM YourTable t
GROUP BY
t.CODE
) t
CROSS APPLY (
SELECT CATEGORY = 100
WHERE cnt <= 2
UNION ALL
SELECT 200
WHERE cnt = 1
) v