Hi PRA,
Refer Below test query for your reference.
SQL
CREATE TABLE #Address(Id INT,HouseNumber VARCHAR(10),FlatNumber VARCHAR(10),WorkPlace VARCHAR(10),Signature VARCHAR(10))
INSERT INTO #Address VALUES(1,11,'','',1)
INSERT INTO #Address VALUES(2,'',21,'',1)
INSERT INTO #Address VALUES(3,'','',1,1)
INSERT INTO #Address VALUES(4,'','',1,1)
INSERT INTO #Address VALUES(5,21,'','',1)
INSERT INTO #Address VALUES(6,'',32,'',1)
INSERT INTO #Address VALUES(7,23,33,1,1)
INSERT INTO #Address VALUES(8,'',34,'',1)
DECLARE @Sign INT
SET @Sign = 1
SELECT COUNT(HouseNumber)HouseNumber, 0 FlatNumber,0 office, 0 free FROM #Address WHERE HouseNumber <> '' GROUP BY SIGNATURE HAVING SIGNATURE = @Sign
UNION all
SELECT 0 HouseNumber,COUNT(FlatNumber)FlatNumber,0 office, 0 free FROM #Address WHERE FlatNumber <> '' GROUP BY SIGNATURE HAVING SIGNATURE = @Sign
UNION all
SELECT 0 HouseNumber,0 FlatNumber,COUNT(WorkPlace)office, 0 free FROM #Address WHERE WorkPlace = 1 GROUP BY SIGNATURE HAVING SIGNATURE = @Sign
UNION all
SELECT 0 HouseNumber,0 FlatNumber, 0 office,ISNULL((SELECT COUNT(WorkPlace) free FROM #Address WHERE WorkPlace = 2 GROUP BY SIGNATURE HAVING SIGNATURE = @Sign),0)
Output
| HouseNumber |
FlatNumber |
office |
free |
| 3 |
0 |
0 |
0 |
| 0 |
4 |
0 |
0 |
| 0 |
0 |
3 |
0 |
| 0 |
0 |
0 |
0 |
