Hi I have a table Space(PropertyName, FloorNumber, No.OfVacantrooms) Now I want to create a report that shows the floor numbers based on the no.of vacant rooms. i.e, I want to see all the floor no.s with 100+ vacancies in one column, 50 to 100 vacancies in next column and 0 to 50 vacancies in the last column. The table structure should be PropName | 100+ | 50-100 | <50 ABC | 2,4,10 | 1,3 | 5,6,7,8 DEF | 1,2 | 3,4 | 5,6 Is there anyway that we can create a report with multiple floor numbers in one cell separated by comma (like 2,4,10). ThanksiPranith

Hi iPranith, Why you have 2 records (meaing ABC and DEF), Isn't should be??:- PropName | 100+ | 50-100 | <50 ABC_DEF | 1,2,4,10 | 1,3,4 | 5,6,7,8 ------------- I have megered ABC & DEF into a single row as ABC_DEF Please let me know if I follow you correctly. Also if possible can you please also share sample table sturcture with values for our better understanding. Thanks Kumar KG, MCTS

Hi Kumar, there are variety of property names, each property name refers to a building. so that we can row group on property name.. thats working fine.. my table is like PropertyName | Floor Number | No.of vacancies BuildingABC | 1 | 56 BuildingABC | 2 | 28 BuildingABC | 3 | 108 BuildingDEF | 1 | 123 BuildingDEF | 2 | 75 BuildingDEF | 3 | 111 Is there anyway to place multiple values in the same cell as follows PropName | 100+vac | <100vac ABC | 3 | 1,2 DEF | 1,3 | 2 Thanks iPranith

Hi iPranith, I would prefer to do such kind of requirement using t-sql scripts as it will be user for the developer and also for the execution time faster for report to render as everything is happening here at the back-end side, try this:- CREATE TABLE #TEMP ( PropertyName VARCHAR(20), FloorNumber INT, Noofvacancies INT ) INSERT INTO #TEMP VALUES ('BuildingABC', 1, 56) INSERT INTO #TEMP VALUES ('BuildingABC', 2, 28) INSERT INTO #TEMP VALUES ('BuildingABC', 3, 108) INSERT INTO #TEMP VALUES ('BuildingDEF', 1, 123) INSERT INTO #TEMP VALUES ('BuildingDEF', 2, 75) INSERT INTO #TEMP VALUES ('BuildingDEF', 3, 111) SELECT RIGHT(PropertyName, 3) AS [PropName], STUFF ( ( SELECT ', ' + CAST(FloorNumber AS VARCHAR(10)) FROM #Temp T1 WHERE Noofvacancies < =100 AND T.PropertyName = T1.PropertyName ORDER BY FloorNumber FOR XML PATH('') ),1,2,'' ) AS [100+vac], STUFF ( ( SELECT ', ' + CAST(FloorNumber AS VARCHAR(10)) FROM #Temp T1 WHERE Noofvacancies > 100 AND T.PropertyName = T1.PropertyName ORDER BY FloorNumber FOR XML PATH('') ),1,2,'' ) AS [<100vac] FROM #TEMP T GROUP BY T.PropertyName DROP TABLE #TEMP Please let us know your feedback. Thanks KumarKG, MCTS

It works. Thanks Kumar.iPranith

Is there any chance that I can add the no.of floors column in the report as well? Property Name | floors Occupied | 100+ | <100 ABC | 3 | 3 | 1,2 DEF | 3 | 1,3 | 2 Thanks for your help..iPranith

CREATE TABLE #TEMP ( PropertyName VARCHAR(20), FloorNumber INT, Noofvacancies INT ) INSERT INTO #TEMP VALUES ('BuildingABC', 1, 56) INSERT INTO #TEMP VALUES ('BuildingABC', 2, 28) INSERT INTO #TEMP VALUES ('BuildingABC', 3, 108) INSERT INTO #TEMP VALUES ('BuildingDEF', 1, 123) INSERT INTO #TEMP VALUES ('BuildingDEF', 2, 75) INSERT INTO #TEMP VALUES ('BuildingDEF', 3, 111) SELECT RIGHT(PropertyName, 3) AS [PropName], COUNT(FloorNumber) AS [floors Occupied], STUFF ( ( SELECT ', ' + CAST(FloorNumber AS VARCHAR(10)) FROM #Temp T1 WHERE Noofvacancies > 100 AND T.PropertyName = T1.PropertyName ORDER BY FloorNumber FOR XML PATH('') ),1,2,'' ) AS [<100vac], STUFF ( ( SELECT ', ' + CAST(FloorNumber AS VARCHAR(10)) FROM #Temp T1 WHERE Noofvacancies < =100 AND T.PropertyName = T1.PropertyName ORDER BY FloorNumber FOR XML PATH('') ),1,2,'' ) AS [100+vac] FROM #TEMP T GROUP BY T.PropertyName DROP TABLE #TEMP try this, Thanks KumarKG, MCTS

Thanks big.. iPranith

Thank You Kumar.. That was very helpfull..