Click here to Skip to main content
15,072,433 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have two different tables as below and have specific requirement of a sql query.
SQL
Table1:
Name   RuleNumber
Tom    1,2
Pete   1,3

Table2:
RuleNumber  Description
1           Rule1
2           Rule2
3           Rule3

How can I get a sql query result something like below
SQL
Name    Description
Tom     Rule1, Rule2
Pete    Rule1, Rule3
Posted
Comments
Sergey Alexandrovich Kryukov 26-Mar-13 20:02pm
   
What, do you mean that RuleNumber ("1,2", "1,3") is a string, as well as Description?
—SA
azeeth 26-Mar-13 20:20pm
   
Yes. They are both string.
Sergey Alexandrovich Kryukov 26-Mar-13 20:22pm
   
Sad.
azeeth 26-Mar-13 20:31pm
   
Maybe I was not clear enough. Table1 has a column for rules which stores comma separated rule numbers. Table2 has the description of those rules with unique rule number. I just need to show the comma separated rule description for each row in Table1 instead of the just rule numbers.
Hope that makes sense.
Sergey Alexandrovich Kryukov 26-Mar-13 20:43pm
   
Quite clear. By the need of this split you (or someone who created this database schema) ruin the whole relational model. Rule names should be foreign keys referencing the rule table. What you want is clear, but working this way makes no sense at all.

Do you know one rule? You can call it Rule0:
Don't leave with broken windows.

Good luck,
—SA
azeeth 26-Mar-13 21:16pm
   
Thanks. I might actually talk to the person to modify the schema to suite relational database design.

Thank you for clarification. You can work around the problem, but it's too disgusting for a human. Change the database schema and migrate the data you have it. If this is beyond your reach, quit the project, company or the whole field. Working with such foolishness does not pay off anyway. I hope you know what is the relational model.

—SA
   
Comments
azeeth 26-Mar-13 20:51pm
   
Thanks for the comment. I understand what you mean and I agree. I have found a work around and it works beautifully. Cheers.
Sergey Alexandrovich Kryukov 26-Mar-13 23:30pm
   
This would be the very best thing you can do, If you need some advice on the schema, please ask.
—SA
If anybody looking for similar things here is the solution.

I needed a custom split function to separate the delimited list, and then use FOR XML PATH to combine the descriptions. Here is the final query
SQL
select  t1.Name,
        STUFF(( SELECT ', ' + Description
                FROM    table2 AS t2 
                WHERE   t2.ruleNumber in (select s from dbo.SplitFunction(t1.RuleNumber, ','))
        ORDER BY ruleNumber
        FOR XML PATH('')), 1, 1, '') as 'Description'
from    table1 t1


Here is the code for the split function.
SQL
create function [dbo].[SplitFunction]
(
    @String     varchar(8000) ,
    @Delimiter  varchar(10)
)
returns @tbl table (s varchar(1000))
as

begin
declare @i int ,
    @j int
    select  @i = 1
    while @i <= len(@String)
    begin
        select  @j = charindex(@Delimiter, @String, @i)
        if @j = 0
        begin
            select  @j = len(@String) + 1
        end
        insert  @tbl select substring(@String, @i, @j - @i)
        select  @i = @j + len(@Delimiter)
    end
    return
end
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900