SQL Server. Join/Pivot/Performance help please

I've created this view that is painfully slow to query and creating performance issues. I think I need to utilize PIVOT but I can't really wrap my head around it...

Here's what I've got currently.

select
    p.instrumentsettingsid,
    sum(case when p1.id is null then 0 else 1 end) as 'Cash',
    sum(case when p2.id is null then 0 else 1 end) as HighInterest,
    sum(case when p3.id is null then 0 else 1 end) as 'ML Loan',
    sum(case when p4.id is null then 0 else 1 end) as 'Investment Note',
    sum(case when p5.id is null then 0 else 1 end) as 'Trading',
    sum(case when p6.id is null then 0 else 1 end) as 'DD Trading',
    sum(case when p7.id is null then 0 else 1 end) as 'ML Trading',
    sum(case when p8.id is null then 0 else 1 end) as 'NMLTrading',
    sum(case when p9.id is null then 0 else 1 end) as 'Individual Custody',
    sum(case when p10.id is null then 0 else 1 end) as 'ML Individual Custody',
    sum(case when p11.id is null then 0 else 1 end) as 'Custody',
    sum(case when p12.id is null then 0 else 1 end) as 'ML Custody Trading',
    sum(case when p13.id is null then 0 else 1 end) as 'Portfolio',
    sum(case when p14.id is null then 0 else 1 end) as 'ML Personal Portfolio',
    sum(case when p15.id is null then 0 else 1 end) as Other,
    i.status
from
    (select distinct instrumentsettingsid from res_db..instrumentproducttypepermissions) p
    inner join res_db..instrumentsettings i on p.instrumentsettingsid = i.id
    left join res_db..instrumentproducttypepermissions p1 on p.instrumentsettingsid = p1.instrumentsettingsid and p1.enabled = 1 and p1.producttypeid = 1
    left join res_db..instrumentproducttypepermissions p2 on p.instrumentsettingsid = p2.instrumentsettingsid and p2.enabled = 1 and p2.producttypeid = 2
    left join res_db..instrumentproducttypepermissions p3 on p.instrumentsettingsid = p3.instrumentsettingsid and p3.enabled = 1 and p3.producttypeid = 3
    left join res_db..instrumentproducttypepermissions p4 on p.instrumentsettingsid = p4.instrumentsettingsid and p4.enabled = 1 and p4.producttypeid = 4
    left join res_db..instrumentproducttypepermissions p5 on p.instrumentsettingsid = p5.instrumentsettingsid and p5.enabled = 1 and p5.producttypeid = 5
    left join res_db..instrumentproducttypepermissions p6 on p.instrumentsettingsid = p6.instrumentsettingsid and p6.enabled = 1 and p6.producttypeid = 6
    left join res_db..instrumentproducttypepermissions p7 on p.instrumentsettingsid = p7.instrumentsettingsid and p7.enabled = 1 and p7.producttypeid = 7
    left join res_db..instrumentproducttypepermissions p8 on p.instrumentsettingsid = p8.instrumentsettingsid and p8.enabled = 1 and p8.producttypeid = 8
    left join res_db..instrumentproducttypepermissions p9 on p.instrumentsettingsid = p9.instrumentsettingsid and p9.enabled = 1 and p9.producttypeid = 9
    left join res_db..instrumentproducttypepermissions p10 on p.instrumentsettingsid = p10.instrumentsettingsid and p10.enabled = 1 and p10.producttypeid = 10
    left join res_db..instrumentproducttypepermissions p11 on p.instrumentsettingsid = p11.instrumentsettingsid and p11.enabled = 1 and p11.producttypeid = 11
    left join res_db..instrumentproducttypepermissions p12 on p.instrumentsettingsid = p12.instrumentsettingsid and p12.enabled = 1 and p12.producttypeid = 12
    left join res_db..instrumentproducttypepermissions p13 on p.instrumentsettingsid = p13.instrumentsettingsid and p13.enabled = 1 and p13.producttypeid = 13
    left join re开发者_JAVA技巧s_db..instrumentproducttypepermissions p14 on p.instrumentsettingsid = p14.instrumentsettingsid and p14.enabled = 1 and p14.producttypeid = 14
    left join res_db..instrumentproducttypepermissions p15 on p.instrumentsettingsid = p15.instrumentsettingsid and p15.enabled = 1 and p15.producttypeid = 15
    group by p.instrumentsettingsid, i.status

Can someone who knows about SQL please tell me (show me?) how I can make this faster/less hideous please.

Also those 15 account names are hardcoded, in a perfect world it would get the product name from the table producttypes

Sorry to ask such an annoying question.

Thank you!

---

Use:

  SELECT p.instrumentsettingsid,
         i.status,
         SUM(CASE WHEN p.producttypeid = 1 THEN 1 ELSE 0 END) as 'Cash',
         SUM(CASE WHEN p.producttypeid = 2 THEN 1 ELSE 0 END) as HighInterest,
         SUM(CASE WHEN p.producttypeid = 3 THEN 1 ELSE 0 END) as 'ML Loan',
         SUM(CASE WHEN p.producttypeid = 4 THEN 1 ELSE 0 END) as 'Investment Note',
         SUM(CASE WHEN p.producttypeid = 5 THEN 1 ELSE 0 END) as 'Trading',
         SUM(CASE WHEN p.producttypeid = 6 THEN 1 ELSE 0 END) as 'DD Trading',
         SUM(CASE WHEN p.producttypeid = 7 THEN 1 ELSE 0 END) as 'ML Trading',
         SUM(CASE WHEN p.producttypeid = 8 THEN 1 ELSE 0 END) as 'NMLTrading',
         SUM(CASE WHEN p.producttypeid = 9 THEN 1 ELSE 0 END) as 'Individual Custody',
         SUM(CASE WHEN p.producttypeid = 10 THEN 1 ELSE 0 END) as 'ML Individual Custody',
         SUM(CASE WHEN p.producttypeid = 11 THEN 1 ELSE 0 END) as 'Custody',
         SUM(CASE WHEN p.producttypeid = 12 THEN 1 ELSE 0 END) as 'ML Custody Trading',
         SUM(CASE WHEN p.producttypeid = 13 THEN 1 ELSE 0 END) as 'Portfolio',
         SUM(CASE WHEN p.producttypeid = 14 THEN 1 ELSE 0 END) as 'ML Personal Portfolio',
         SUM(CASE WHEN p.producttypeid = 15 THEN 1 ELSE 0 END) as Other,
    FROM res_db..instrumentproducttypepermissions p
    JOIN res_db..instrumentsettings i ON i.id = p.instrumentsettingsid
   WHERE p.enabled = 1
     AND p.producttypeid BETWEEN 1 AND 15
GROUP BY p.instrumentsettingsid, i.status

No need for the LEFT JOIN...

Also those 15 account names are hardcoded, in a perfect world it would get the product name from the table producttypes

Dynamic columns requires dynamic SQL.

---

Even using Sql Server PIVOT you will have to know the Column names before hand, so no luck there, unless you make use of Pivots with Dynamic Columns or Dynamic PIVOT