SQL subquery poor perf - help to rewrite

Question

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.

Answer 1

Select m.PolNr  
, max(Case when d.Category='Holder' then d.value else null end)  as HolderName  
, max(Case when d.Category='Status' then d.value else null end) as PolStatus  
--there are around 20 such similar subqueries --for the rest of the select fields  
  
From tblPolMaster m  
join from tblPolSpec d ON m.PolNr=d.PolNr  
Group by m.PolNr  

Answer 2

Hi @Newbie Newbie
It seems that you need a PIVOT query, check following two queries:

--1  
SELECT M.PolNr  
      ,MAX(CASE WHEN D.Category='Holder' THEN D.value ELSE NULL END)AS HolderName  
	  ,MAX(CASE WHEN D.Category='Status' THEN D.value ELSE NULL END)AS PolStatus  
	  ,...  
FROM tblPolMaster M JOIN tblPolSpec D ON M.PolNr=D.PolNr  
GROUP BY M.PolNr  
  
--2 Using PIVOT  
SELECT PolNr  
      ,[Holder] AS HolderName  
	  ,[Status] AS PolStatus  
	  ,...  
FROM   
(  
 SELECT M.PolNr,Category,value  
 FROM tblPolMaster M JOIN tblPolSpec D ON M.PolNr=D.PolNr  
)  
PIVOT(MAX(value) FOR Category IN ([Holder],[Status],...))p  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.