Write a query to create a pivot table to display the number of customers taken policies with policy_value 250000,75000,45000. Specify the name of the first column in the pivot table as 'customer_count' with literal value as customerCount. Use: PIVOT

As a solution I have used the following code.

select id as customer_count, [250000], [75000], [45000]
from 
(
select * from customer_policy
)
PIVOT
(count(id) FOR policy_value IN [250000], [75000], [45000])
GO

But getting error as Incorrect syntax near the keyword 'PIVOT'.

EDIT Output is required in the below format

CodePudding user response：

These are typographical error really. You have several errors in the above SQL.

1. You don't alias your subquery
2. You are missing parenthesis (()) around your IN clause
3. You don't alias your pivot
4. ID doesn't exist in the returned columns for your pivot, so needs to be removed

If you fix this, you get a valid query:

SELECT [250000],
       [75000],
       [45000]
FROM (SELECT * FROM customer_policy) cp
PIVOT (COUNT(id)
       FOR policy_value IN ([250000], [75000], [45000])) p;

Though, in true, you don't actually need the subquery:

SELECT [250000],
       [75000],
       [45000]
FROM customer_policy cp
    PIVOT (COUNT(id)
           FOR policy_value IN ([250000], [75000], [45000])) p;

You may need other columns in your SELECT, as you use a *, I don't know what other columns you want to expose.

CodePudding user response：

Below is the answer to the question

SELECT 'customerCount' AS customer_count,
    [250000],
    [75000],
    [45000]
FROM (SELECT id, policy_value FROM customer_policy) cp
PIVOT (COUNT(id) 
        FOR policy_value IN ([250000], [75000], [45000])) p;