数据表结构
物品 供应商
A A1
A A2
A A3
B B1
B B2
B B3
输出结果
物品 供应商1 供应商2 供应商3
A A1 A2 A3
供应商数量最大为10个。最小为0
求帮助
------解决方案--------------------
with t as
(select 'A' GOODS, 'A1' COMPANY
FROM DUAL
UNION ALL
select 'A' GOODS, 'A2' COMPANY
FROM DUAL
UNION ALL
select 'A' GOODS, 'A3' COMPANY
FROM DUAL
UNION ALL
select 'B' GOODS, 'B1' COMPANY
FROM DUAL
UNION ALL
select 'B' GOODS, 'B2' COMPANY
FROM DUAL
UNION ALL
select 'B' GOODS, 'B3' COMPANY
FROM DUAL
UNION ALL
select 'C' GOODS, 'C1' COMPANY
FROM DUAL)
SELECT *
FROM (SELECT T.*,
ROW_NUMBER() OVER(PARTITION BY GOODS ORDER BY COMPANY) RN
FROM T) PIVOT(MAX(COMPANY) FOR RN IN(1 AS COMPANY1,
2 AS COMPANY2,
3 AS COMPANY3,
4 AS COMPANY4,
5 AS COMPANY5,
6 AS COMPANY6,
7 AS COMPANY7,
8 AS COMPANY8,
9 AS COMPANY9,
10 AS COMPANY10));