i would suggest a better solution but as far as what you want i will give you first then see my solution
DECLARE @Customer AS TABLE (Customerid INT, CustomerName VARCHAR(15))
DECLARE @Project AS TABLE (Customerid INT, DocStatus INT)
INSERT INTO @Customer
VALUES
(1,'Customer1'),(2,'Customer2'),(3,'Customer3'),(4,'Customer4'),(5,'Customer5'),
(6,'Customer6'),(7,'Customer7'),(8,'Customer8'),(9,'Customer9'),(10,'Customer10'),
(11,'Customer11'),(12,'Customer12'),(13,'Customer13'),(14,'Customer14')
INSERT INTO @Project
VALUES
(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),
(8,1),(9,2),(10,3),(11,4),(12,5),(13,6),(14,7),
(1,2),(2,3),(3,4),(4,5),(5,6),(6,7),(7,1),
(8,2),(9,3),(10,4),(11,5),(12,6),(13,7),(14,1)
SELECT P.DocStatus, C.CustomerName, P.CustomerID
FROM @Project P
INNER JOIN @Customer C
ON P.CustomerID = C.CustomerID
UNION ALL
SELECT 8 AS Docstatus, c.CustomerName, COUNT(*)
FROM @Project P
INNER JOIN @Customer C
ON P.CustomerID = C.CustomerID
GROUP BY
C.Customerid, c.CustomerName
SELECT CustomerName, [0] AS Pending, [1] AS Save_As_Draft, [2] AS Spec_Confirmed, [3] AS
Request_Revision, [4] AS Withdraw, [5] AS Pending_RND, [6] AS Pending_QC, [7] AS
Manday_Confirmed,[8] AS Total
FROM (
SELECT P.DocStatus, C.CustomerName, P.CustomerID, 1 AS customercount
FROM @Project P
INNER JOIN @Customer C
ON P.CustomerID = C.CustomerID
UNION ALL
select 8 AS Docstatus, c.CustomerName,c.Customerid, COUNT (p.Customerid)
FROM @Project P
INNER JOIN @Customer C
ON P.CustomerID = C.CustomerID
GROUP BY C.Customerid, c.CustomerName
) p
PIVOT(
SUM(customercount) FOR DocStatus IN ([0], [1], [2], [3], [4], [5], [6], [7],[8])
) AS pvt;
SELECT C.CustomerName,
SUM(CASE WHEN docStatus =0 THEN 1 ELSE 0 END) AS Pending,
SUM(CASE WHEN docStatus =1 THEN 1 ELSE 0 END) AS Save_As_Draft,
SUM(CASE WHEN docStatus =2 THEN 1 ELSE 0 END) AS Spec_Confirmed,
SUM(CASE WHEN docStatus =3 THEN 1 ELSE 0 END) AS Request_Revision,
SUM(CASE WHEN docStatus =4 THEN 1 ELSE 0 END) AS Withdraw,
SUM(CASE WHEN docStatus =5 THEN 1 ELSE 0 END) AS Pending_RND,
SUM(CASE WHEN docStatus =6 THEN 1 ELSE 0 END) AS Pending_QC,
SUM(CASE WHEN docStatus =7 THEN 1 ELSE 0 END) AS Manday_Confirmed,
SUM(1) AS Total
FROM @Project P
INNER JOIN @Customer C
ON P.CustomerID = C.CustomerID
GROUP BY c.CustomerName
well this is what you want i think hopt which one is better in looking and performance wise