i have tow tables. one is Feature table and another one is Property table
The feature table contain following feilds and data
feauture id is primary key
Featureid Type
1Garden
2Parking
3Swimming pool
4Balconys
And property table contains data like below
Propertyid is primary key
Propertyid Feature
1 1,2,4
2 3,4
3 1,3,4
4 2,3,4
5 1,4
i want property count feature wise
like below
Feature PropertyCount
Garden 3
Parking 2
Swimming pool 3
Balconys 5
How to join these two tables and property count group by feature Type
Rahul BansalPosted Aug 22, 2014, 6:31 AM
create FUNCTION [dbo].[Split]
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END
After that run this query--
select type as Feature,Count(*) as PropertyCount
from feature f inner join property p on f.Featureid <> p.Feature
where f.Featureid in (select value from split(p.Feature,','))
group by type