prod item type ux
1 a x z1
2 a y z1
3 b x z1
4 b y z1
需要的结果集
=============
prod item prod2 ux
-----------------------------
1 a 2 z1
3 b 4 z1
---------------------
prod 列的值 显示的都是type=x的
prod2列显示的值都是type=y的
而对应关系就是通过 item关联
------解决思路----------------------
WITH t(prod , item , type , ux)
AS
(
SELECT 1 , 'a' , 'x' , 'z1' UNION all
SELECT 2 , 'a', 'y', 'z1' UNION all
SELECT 3 , 'b', 'x' , 'z1' UNION all
SELECT 4 , 'b', 'y' , 'z1'
)
SELECT item,ux,
max(CASE WHEN type='x' THEN prod ELSE NULL end) prod,
max(CASE WHEN type='y' THEN prod ELSE NULL end) prod2
FROM t
GROUP BY item,ux