CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
DrGenius
DrGenius

Reputation: 967

Prefixed columns are not allowed in the column list of a PIVOT operator

I have this query:

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 ;

But it returns this error:

SQL Error [254] [S0001]: Prefixed columns are not allowed in the column list of a PIVOT operator

Sample Data:

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

To:

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

Upvotes: 0

Views: 421

Answers (1)

Thom A
Thom A

Reputation: 95829

What you have isn't a PIVOT. YOu just need a couple of CASE expressions:

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;

Upvotes: 1

Related Questions