For your next question I'd ask you to create a MCVE to reproduce your issue. The minimum was some sample data, best provided as DDL and DML.
I've done this for you this time. This is not your exact problem, but it will help to demonstrate the approach.
First we need a mockup table with some data:
DECLARE @tbl TABLE(ID INT IDENTITY, GroupID INT, SubId CHAR(1),Sub2Id CHAR(1), SomeValue VARCHAR(100));
INSERT INTO @tbl VALUES
(1,'a','x','a with x is First in 1')
,(1,'b','y','b with y is Second in 1')
,(1,'c','z','c with z is Third in 1')
,(2,'b','x','b with x is First in 2')
,(2,'c','z','c with z is Second in 2')
,(3,'a','y','a with y is First in 3');
--This query has the issue you are talking about:
SELECT p.*
FROM
(
SELECT tbl.SubId
,tbl.GroupID
,tbl.Sub2Id
,tbl.SomeValue
FROM @tbl tbl
) t
PIVOT
(
MAX(SomeValue) FOR SubId IN(a,b,c)
) p
ORDER BY GroupId,Sub2Id;
The result
+---------+--------+------------------------+-------------------------+-------------------------+
| GroupID | Sub2Id | a | b | c |
+---------+--------+------------------------+-------------------------+-------------------------+
| 1 | x | a with x is First in 1 | NULL | NULL |
+---------+--------+------------------------+-------------------------+-------------------------+
| 1 | y | NULL | b with y is Second in 1 | NULL |
+---------+--------+------------------------+-------------------------+-------------------------+
| 1 | z | NULL | NULL | c with z is Third in 1 |
+---------+--------+------------------------+-------------------------+-------------------------+
| 2 | x | NULL | b with x is First in 2 | NULL |
+---------+--------+------------------------+-------------------------+-------------------------+
| 2 | z | NULL | NULL | c with z is Second in 2 |
+---------+--------+------------------------+-------------------------+-------------------------+
| 3 | y | a with y is First in 3 | NULL | NULL |
+---------+--------+------------------------+-------------------------+-------------------------+
You can see, that each GroupId is mentioned as often as there is data. Group 1 is represented in 3 rows x,y and z, while 2 shows up with z and x and 3 only with y.
What you need, is a fixed set with all rows you want to see in any case. If you can be sure, that any combination you want to see exists at least once in your set, you can extract this from the data directly, otherwise you'd have to maintain a table providing such a fix set. In the example I assume, that the existing data can provide all what I need.
--Create a combination of all existing Groups *cross joined* with all existing subs.
WITH AllCombinations AS
(
SELECT *
FROM (SELECT GroupID FROM @tbl GROUP BY GroupID) t1
CROSS JOIN (SELECT Sub2Id FROM @tbl GROUP BY Sub2Id) t2
)
SELECT p.*
FROM
(
SELECT tbl.SubId
,combos.GroupID
,combos.Sub2Id
,tbl.SomeValue
FROM AllCombinations combos
LEFT JOIN @tbl tbl ON combos.GroupID=tbl.GroupID AND combos.Sub2Id=tbl.Sub2Id
) t
PIVOT
(
MAX(SomeValue) FOR SubId IN(a,b,c)
) p
ORDER BY GroupId,Sub2Id;
You see, that I LEFT JOIN the combination set with the actual table. And I pick the values with the combos alias and not with tbl. This will ensure, that these values are within the resulting set and will show up in the pivoted list.
The result
+---------+--------+------------------------+-------------------------+-------------------------+
| GroupID | Sub2Id | a | b | c |
+---------+--------+------------------------+-------------------------+-------------------------+
| 1 | x | a with x is First in 1 | NULL | NULL |
+---------+--------+------------------------+-------------------------+-------------------------+
| 1 | y | NULL | b with y is Second in 1 | NULL |
+---------+--------+------------------------+-------------------------+-------------------------+
| 1 | z | NULL | NULL | c with z is Third in 1 |
+---------+--------+------------------------+-------------------------+-------------------------+
| 2 | x | NULL | b with x is First in 2 | NULL |
+---------+--------+------------------------+-------------------------+-------------------------+
| 2 | y | NULL | NULL | NULL |
+---------+--------+------------------------+-------------------------+-------------------------+
| 2 | z | NULL | NULL | c with z is Second in 2 |
+---------+--------+------------------------+-------------------------+-------------------------+
| 3 | x | NULL | NULL | NULL |
+---------+--------+------------------------+-------------------------+-------------------------+
| 3 | y | a with y is First in 3 | NULL | NULL |
+---------+--------+------------------------+-------------------------+-------------------------+
| 3 | z | NULL | NULL | NULL |
+---------+--------+------------------------+-------------------------+-------------------------+
UPDATE
I tried to fix your query, but I cannot test it. So this is flying blindfolded through a dense forest in the middle of the night ;-)
CREATE TABLE #propCodes (PropertyCode nvarchar(15));
CREATE TABLE #FYs (FY nvarchar(10));
CREATE TABLE #Markets(Market nvarchar(255));
INSERT INTO #propCodes VALUES('CDM');
INSERT INTO #FYs VALUES('FY 18-19'),('FY 19-20');
INSERT INTO #Markets VALUES
('France'),
('United Kingdom'),
('Germany'),
('Reunion'),
('South Africa'),
('Russia'),
('Middle East'),
('France');
/* Check this to see the *cartesian product*
SELECT *
FROM #propCodes
CROSS JOIN #FYs
CROSS JOIN #Markets;
*/
;WITH temp1 AS
(
SELECT *
FROM #propCodes
CROSS JOIN #FYs
CROSS JOIN #Markets
)
,Aggregated AS
(
SELECT
[PropertyCode],
[FY],
[Market],
ISNULL((SUM([Package Revenue Excl VAT])/SUM([GN])),0) AS 'GADR',
FORMAT([MTH], 'MMM') AS 'MthTxt'
FROM [QueryType2_v06feb2019_TBL]
WHERE [MTH] BETWEEN '2018-07-01' AND '2020-06-01'
GROUP BY [PropertyCode], [MTH], a.[FY], [Market]
)
SELECT PivotTable.*
FROM
(
SELECT *
FROM [temp1] a
LEFT JOIN Aggregated b ON (b.PropertyCode = a.PropertyCode AND b.FY = a.FY AND b.Market = a.Market)
)AS SourceTable
PIVOT
(
AVG([GADR]) FOR [MthTxt] IN ([Jul], [Aug], [Sep], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [May], [Jun])
) AS PivotTable;
I replaced your single temp table by tree easy to maintain tables. Check the out-commented SELECT to see what happens...
And I introduced one more CTE to separate your aggregations/computations from the pivoting the LEFT JOIN.
Good luck. If this does not work, please provide sample data in consumable format reduced to the needed minimum...
