Split and concatenate values in SQL

Question

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 26-Mar-13 13:56pm

azeeth

Add a Solution

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.

2 solutions

Solution 1

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

Posted 26-Mar-13 14:27pm

Sergey Alexandrovich Kryukov

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

azeeth · Accepted Answer · 2013-03-26T15:33:00

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