未获得预期结果

kuarbcqp 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(381)

我有这样的数据：

我想要这个-我正在尝试pivot，但没有得到预期的结果：

查询是

SELECT AttendeeID,[Quantity1],[PROD1],[Quantity2],[PROD2],[Quantity3],[PROD3] FROM
  (SELECT * ,
  row_number() over(partition by  AttendeeID order by AttendeeID)rn
  from #ProductTestingwithPosition2) TT
  PIVOT
  (MAX(product) for ProductPosition in ([PROD1],[PROD2],[PROD3])) AS Tab2
  PIVOT
  (sum(Quantity) for QuantityPosition in ([Quantity1],[Quantity2],[Quantity3])) AS Tab3

我得到这个输出：

sql sql-server pivot pivot-table

来源：https://stackoverflow.com/questions/62020217/expected-result-is-not-getting

1条答案

按热度按时间

qcbq4gxm1#

只需使用条件聚合：

SELECT AttendeeID,
       MAX(CASE WHEN ProductPosition = 'PROD1' THEN product END) as prod1,
       MAX(CASE WHEN QuantityPosition = 'QUANTITY1' THEN quantity END) as quantity1,
       MAX(CASE WHEN ProductPosition = 'PROD2' THEN product END) as prod2,
       MAX(CASE WHEN QuantityPosition = 'QUANTITY2' THEN quantity END) as quantity2,
       MAX(CASE WHEN ProductPosition = 'PROD3' THEN product END) as prod3,
       MAX(CASE WHEN QuantityPosition = 'QUANTITY3' THEN quantity END) as quantity3
FROM (SELECT ptp.* ,
             row_number() over (partition by  AttendeeID, ProductPosition order by AttendeeID) as seqnum
      FROM #ProductTestingwithPosition2 ptp
     ) ptp
GROUP BY AttendeeID, seqnum;
``` `PIVOT` --除了不规范之外，他还非常挑剔。条件聚合功能更强大，更不容易出错。

赞(0）回复(0）举报 2021-07-26

我来回答

未获得预期结果

1条答案

相关问题

热门标签

最新问答