sql - PIVOT 运算符的列列表中不允许有前缀列

Question

我有这个查询：

SELECT
COUNT(DISTINCT DIAL) as CALL_SUMMARY,APP,DEST_URI
FROM
test.dbs
PIVOT (COUNT(DIAL) FOR APP IN(phone, mobile)) as log_status_new
WHERE
ID_DATE >= '2019-07-08 16:41:42'
AND  ID_DATE < '2020-07-08 16:41:42'
GROUP BY APP,DEST_URI ;

但它返回此错误：

SQL 错误 [254] [S0001]: PIVOT 运算符的列列表中不允许有前缀列

样本数据：

+----------+----------+----------+
|   DIAL   |   APP    | DEST_URI |
+----------+----------+----------+
| 5656     | phone    |        1 |
| 5657     | phone    |        2 |
| 5456     | mobile   |        6 |
| 5898     | mobile   |        9 |
| 5656     | phone    |        4 |
+----------+----------+----------+

到：

+----------+--------+--------+------------+
| DIAL     | PHONE  | MOBILE |  DEST_URI  |
+----------+--------+--------+------------+
| 5656     | 1      |        |   1        |
| 5657     | 1      |        |   2        |
| 5456     |        |  1     |   6        |
| 5898     |        |  1     |   9        |
| 5656     | 1      |        |   4        |
+----------+--------+--------+------------+

标签： sqlsql-servertsqlpivot

Answer 1

你所拥有的不是PIVOT. 你只需要几个CASE表达式：

SELECT DIAL,
       CASE APP WHEN 'phone' THEN 1 END AS PHONE,
       CASE APP WHEN 'mobile' THEN 1 END AS MOBILE,
       DEST_URI
FROM dbo.YourTable;