Question

关于sql 列转行，总是提示标识符无效

这个是代码 WITH A AS (SELECT P_ID,NAME FROM A_TEST_PERSON T),WITH B AS (SELECT ORDERPRICE, CUSTOMER_ID,O_ID FROM A_TEST_ORDERS O)SELECT NAME,产品1,产品2,产品3,产品4,产品5,产品6,产品7,产品8,产品9,产品10,产品11,产品12,SUM(to_number(ORDERPRICE)) AS PRICETOTALFROM A,BPIVOT (MAX(ORDERPRICE) FOR O_ID IN('产品1' AS 产品1,'产品2' AS 产品2,'产品3' AS 产品3,'产品4'AS 产品4,'产品5' AS 产品5,'产品6' AS 产品6,'产品7' AS 产品7,'产品8' AS 产品8,'产品9' AS 产品9,'产品10' AS 产品10,'产品11' AS 产品11,'产品12' AS 产品12))

Answer 1

Pivot的规则是：
任何仅在pivot子句中的引用的列，不能用在select列表中，
任何仅在pivot for子句中引用的列，不能用在select列表中。

从执行顺序来讲，先pivot，再select，你的pivot里面的MAX(ORDERPRICE)会变成 产樱逗品1_MAX,产品2_MAX等，select自然会告诉你标告竖识符无效，你也不应该写它，还有脊友卖就是那些产品1，产品2也不用写的