Mauricio, Can you post the current version of the SQL code you are using?

I have attached a small sample expected output as CSV and a screen shot. Level 2 & 3 are only one row but the other rows can be one...

I don't quite understand the purpose of this code:

 OUTER APPLY (SELECT NR, 
           ...

I don't quite understand the purpose of this code:

 OUTER APPLY (SELECT NR, 
             ...

You can't rely on the percentage figures given in an execution plan. I rely on SET STATISTICS IO, TIME ON and try to minimise the figures given in that.

Can you...

Just put it in an OUTER APPLY:

Into POInfo
From MeritusMmPO as PO 
Left Join MeritusMmPOLines as POL 
ON PO.SourceID = POL.SourceID AND
PO.PurchaseOrderID = POL.PurchaseOrderID  
OUTER APPLY(SELECT POC.SourceID,
...

Couldn't you just write it without PIVOT like this?

;with MeritusMmPoComments as
(
    Select * FROM (VALUES
    ('WAC', 494819,1,'ALL','Joe Fleichman/81-2035','2017-08-30 13:14:00'),
  ...

If you give an example of the input you have and the output you expect I might be able to help. From your description I can't really work out what...

In situations like this SQL Server will use the most local column it can find if there are no aliases. So if the column name is present on both #PROVINCES...

Your query is equivalent to this:

SELECT *
  FROM #CITIES
 WHERE ProvinceCode IN (SELECT ProvinceCode
            ...

Select SourceId, PurchasOrderID, AppearsOn, RowUpdateDateTime, 
       [1] comment1,
       [2] comment2,
     ...

I came up with the following self referencing CTE but its not moving along the levels, so IDK.

With CTE
  AS(
SELECT Dimensions,Management,[Description], [Level]
FROM [dbo].[Hier_table]
WHERE Dimensions =...

Here's a way of writing it without ORs, but I'm not sure it's any better:

WHERE iv.AccountCode = @AccountCode
  AND iv.ItemID = @ItemID
  AND iv.VarianceID =...

• This reply was modified 5 years, 7 months ago by Jonathan AC Roberts. Reason: Added EXCEPT

WHERE 1 = CASE

WHEN T.record_Key_4 = '1' OR T.Record_Key_5 NOT IN('I', 'X') THEN CASE WHEN PS.popup_gid = 2 THEN 1 eLSE 0 END

ELSE CASE WHEN PS.popup_gid = 5 THEN...

Yes, I was...