group by comma separated value

Question

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 Bansal · Answer

Just create a function for split like- create FUNCTION [dbo].[Split] ( @List nvarchar(2000) @SplitOn nvarchar(5) ) RETURNS @RtnValue table ( Id int identity(11) Value nvarchar(100) ) AS BEGIN While (Charindex(@SplitOn@List)>0) Begin Insert Into @RtnValue (value) Select Value = ltrim(rtrim(Substring(@List1Charindex(@SplitOn@List)-1))) Set @List = Substring(@ListCharindex(@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 FeatureCount(*) 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