SQL Subquery need to rewrite to optimise

1

I have a sql query as below which takes endless time to execute. Any help in rewriting this query to better way would be appreciated greatly.

Select PolNr
, (select value from tblPolSpec d
where m.PolNr=d.PolNr
and d. Category='Holder' ) as HolderName
, (select value from tblPolSpec d
where m.PolNr=d.PolNr
and d. Category='Status' ) as PolStatus
--there are around 20 such similar subqueries --for the rest of the select fields

From tblPolMaster m

Please help to rewrite this query in better way.
Thanks in advance.

2

Welcome

Does tblPolMaster always have a corresponding record in tblPolSpec

3 

Select PolNr
, d.HolderName
, d.PolStatus
/*m...*/
--there are around 20 such similar subqueries --for the rest of the select fields

From tblPolMaster m
cross apply (
    select 
        max(case when Category='Holder' then value end) as HolderName
        ,max(case when Category='Status' then value end) as PolStatus 
        /*,.... */
    from tblPolSpec d
    where m.PolNr=d.PolNr
) as d
4

Not always. It may or may not have matching record.

5

Sorry; then change "CROSS APPLY" to "OUTER APPLLY":


Select PolNr
, d.HolderName
, d.PolStatus
/*m...*/
--there are around 20 such similar subqueries --for the rest of the select fields
From tblPolMaster m
outer apply ( --<<--<<--
    select 
        max(case when Category='Holder' then value end) as HolderName
        ,max(case when Category='Status' then value end) as PolStatus 
        /*,.... */
    from tblPolSpec d
    where m.PolNr=d.PolNr
) as d