Rules Engine - Rows To Column Question 1

[SgtJarrow]

I am working on a Rules Engine for a portion of logic that needs to be editable by the users. This Rules Engine will be dynamic enough for the users to be able to change a lot of the logic without IT intervention. I have the following procedure that gets me the combined logic for one rule:

--RuleId passed in as parameter

declare @IfSide varchar(5000)
declare @ThenSide varchar(5000)

select @IfSide = ISNULL(@IfSide + ' AND ' + k.Keyword + ' ' + o.Operator + ' "' + csr.ValueText + '"', k.Keyword + ' ' + o.Operator + ' "' + csr.ValueText + '"')
from dbo.ConfirmSubRules csr
inner join dbo.Keywords k on k.KeywordId = csr.KeywordId
inner join dbo.Operators o on o.OperatorId = csr.OperatorId
where csr.RuleId = @RuleId
	and csr.SubRuleTypeId = 1

select @ThenSide = ISNULL(@ThenSide + ' AND ' + k.Keyword + ' ' + csr.ValueText, k.Keyword + ' ' + csr.ValueText)
from dbo.ConfirmSubRules csr
inner join dbo.Keywords k on k.KeywordId = csr.KeywordId
where csr.RuleId = @RuleId
	and csr.SubRuleTypeId = 2

select @IfSide 'IfSide', @ThenSide 'ThenSide'

Results from the above look like:

IfSide	                                        ThenSide
Basis equals "no load at NAV & trans fee"	Bullet 3204 AND Paragraph 3204 AND Bullet 3205

This works great for one rule at a time. But I need to display all rules in a RuleSet at once on the screen. How can I group this going up one level??? Something like:

--RuleSetId passed in as parameter

select @IfSide = ISNULL(@IfSide + ' AND ' + k.Keyword + ' ' + o.Operator + ' "' + csr.ValueText + '"', k.Keyword + ' ' + o.Operator + ' "' + csr.ValueText + '"')
from dbo.ConfirmSubRules csr
inner join dbo.Keywords k on k.KeywordId = csr.KeywordId
inner join dbo.Operators o on o.OperatorId = csr.OperatorId
[red]inner join dbo.ConfirmRules r on r.RuleId = csr.RuleId
where r.RuleSetId = @RuleSetId[/red]
	and csr.SubRuleTypeId = 1
[red]Group By RuleId[/red]

Thanks.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[SgtJarrow]

Rereading the above...it might be a bit confusing. Here's the same example in a different format.

Given the following query:

select 
	r.RuleSetId
	, csr.RuleId
	, srt.SubRuleType
	, k.Keyword + ' ' + o.Operator + ' "' + csr.ValueText + '"' 'SubRuleText'
from dbo.ConfirmSubRules csr
inner join dbo.SubRuleTypes srt on srt.SubRuleTypeId = csr.SubRuleTypeId
inner join dbo.Keywords k on k.KeywordId = csr.KeywordId
inner join dbo.Operators o on o.OperatorId = csr.OperatorId
inner join dbo.ConfirmRules r on r.RuleId = csr.RuleId
order by r.RuleId, csr.RuleId

I get the following results:

RuleSetId	RuleId	SubRuleType	SubRuleText
1	        1	Condition	Basis equals "no load at NAV & trans fee"
1	        1	Action	        Bullet equals "3204"
1	        1	Action	        Paragraph equals "3204"
1	        1	Action	        Bullet equals "3205"
1	        2	Condition	Basis equals "no load at NAV & no trans fee"
1	        2	Action	        Bullet equals "3203"
1	        2	Action	        Paragraph equals "3203"

I would like to have the following results instead:

RuleSetId	RuleId	Condition	                    Action
1	        1	Basis equals "no load at NAV & trans fee"   Bullet equals "3204" AND Paragraph equals "3204" AND Bullet equals "3205"
1	        2	Basis equals "no load at NAV & no trans fee"   Bullet equals "3203" AND Paragraph equals "3203"

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[SgtJarrow]

OK...I found the solution. The below code works as I need. I will create the function on my server and then just need the basic select statement for my datagrids....Thanks if you looked at this.

create function dbo.GetSubRules
(
	@RuleId int
	, @SubRuleTypeId int
)
returns varchar(500)
as
begin
    DECLARE @r VARCHAR(500) 
    SELECT @r = ISNULL(@r+ ' AND ', '')
		+ k.Keyword + ' ' + o.Operator + ' "' + csr.ValueText + '"' 
        FROM dbo.ConfirmSubRules csr
		inner join dbo.Keywords k on k.KeywordId = csr.KeywordId
		inner join dbo.Operators o on o.OperatorId = csr.OperatorId
        WHERE RuleId = @RuleId
			AND csr.SubRuleTypeId = @SubRuleTypeId
    RETURN @r 
end
go
 
select
	RuleId,
	dbo.GetSubRules(RuleId, 1) 'IfSide',
	dbo.GetSubRules(RuleId, 2) 'ThenSide'
from
(
	select
		RuleId
	from
		dbo.ConfirmSubRules
	group by
		RuleId
) i 

drop function dbo.GetSubRules

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer