SQL Query using inner join

Question

CategoryTable

   Code   Name
    1      Food
    2      Non-Food

Existing Table Consists list of category, as for example, I have two only Food and Non-Food

As challenge, I am assigning tenants with category or categories (multiple assignment, as there are tenants which are categorized as food and non-food). I i used to insert Tenant and Code to a new table creating this output

TenantAssignTable

Tenant    Code   
Tenant1   1,2    
Tenant2   1

What I need to do, is to load the TenantAssingTable to gridview consisting the Name of the CategoryCode too like this

Desired Output

   Tenant    CCode    Name
    Tenant1   1,2    Food,Non-Food
    Tenant2   1      Food

I used inner join in my code, but this is limited as I have a string of combined code in Code column.

Select a.tenant, a.ccode, b.name
from TenantAssignTable a inner join CategoryTable b
on a.CCode = b.code

Is there anyway to achieve this kind of output? I know that this is unusual in SQL coding but this is what is challenge as what the desired output is concerned and needs which is to have a multiple assignment of category to a single tenant.

Thanks in advance!

Don't store those Code's as comma separated items. Will only cause you lots of trouble. — jarlh
– jarlh, Commented Sep 22, 2016 at 12:28
I do have a split function already, but dont know how to incorporate it to the code @SandipPatel — rickyProgrammer
– rickyProgrammer, Commented Sep 22, 2016 at 12:30
You could also do nested replaces, but a split function is better. Normalizing your data would be best, but it sounds like it's beyond your experience. You should read a book on Database Design. The WROX book is excellent. — Tab Alleman
– Tab Alleman, Commented Sep 22, 2016 at 12:34

neer · Accepted Answer · 2016-09-22 13:03:42Z

2

Think simple;

You can with LIKE and XML PATH

DECLARE @CategoryTable TABLE (Code VARCHAR(50), Name VARCHAR(50))
INSERT INTO @CategoryTable
VALUES  
('1', 'Food'),
('2', 'Non-Food')

DECLARE @TenantAssignTable TABLE (Tenant VARCHAR(50), Code VARCHAR(50))
INSERT INTO @TenantAssignTable
VALUES  
('Tenant1', '1,2'),
('Tenant2', '1')

SELECT
    T.Tenant ,
    T.Code,
    STUFF(
        (SELECT
            ',' + C.Name
        FROM
            @CategoryTable C
        WHERE
            ',' + REPLACE(T.Code, ' ', '') + ',' LIKE '%,' + C.Code + ',%'
        FOR XML PATH('')
    ), 1, 1, '') A
FROM
    @TenantAssignTable T

Result:

Tenant          Code         A
--------------- ------------ ---------------
Tenant1         1,2          Food,Non-Food
Tenant2         1            Food

edited Sep 22, 2016 at 13:03

answered Sep 22, 2016 at 12:52

neer

4,0926 gold badges22 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

gofr1 Over a year ago

Nice, but if we add (10, 'Something') in @CategoryTable and ('Tenant3', '10'). Your query will return Tenant3 | 10 | Food,Something :(

rickyProgrammer Over a year ago

HI @NEER I am trying your code but it is giving me this error: Error converting data type varchar to numeric.

neer Over a year ago

Can you show column type of table? I can edit answer then.

rickyProgrammer Over a year ago

@NEER I already get it. but only problem the the A column result is being separated by so many spaces every after comma. Example: instead of this output: Food,Non-Food, it is resulting like this: Food (many spaces) ,Non-Food

neer Over a year ago

Whats type of column?

gofr1 · Accepted Answer · 2016-09-22 12:45:07Z

You can use some XML transformations:

DECLARE @x xml

SELECT @x = (
    SELECT CAST('<t name="'+a.tenant +'"><a>'+REPLACE(a.code,',','</a><a>') +'</a></t>' as xml)
    FROM TenantAssignTable a 
    FOR XML PATH('')
)

;WITH cte AS (
SELECT  t.v.value('../@name','nvarchar(max)') as Tenant,
        t.v.value('.','int') as CCode,
        ct.Name
FROM @x.nodes('/t/a') as t(v)
INNER JOIN CategoryTable ct
    ON ct.Code = t.v.value('.','int')
)

SELECT DISTINCT
            c.Tenant,
            STUFF((SELECT ','+CAST(CCode as nvarchar(10))
            FROM cte
            WHERE c.Tenant = Tenant
            FOR XML PATH('')
            ),1,1,'') as CCode,
            STUFF((SELECT ','+Name
            FROM cte
            WHERE c.Tenant = Tenant
            FOR XML PATH('')
            ),1,1,'') as Name
FROM cte c

Output:

Tenant  CCode   Name
Tenant1 1,2     Food,Non-Food
Tenant2 1       Food

The first part (defining @x variable) will bring your table to this kind of XML:

<t name="Tenant1">
  <a>1</a>
  <a>2</a>
</t>
<t name="Tenant2">
  <a>1</a>
</t>

Then in CTE part we join XML with table of categories. And after all get data from CTE with the help of FOR XML PATH.

Sandip - Frontend Developer · Accepted Answer · 2016-09-22 12:47:43Z

Create Function as below which return Table from separated Value

CREATE FUNCTION [dbo].[fnSplit]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)

Create Function as below which return comma separated Name

CREATE FUNCTION [dbo].[GetCommaSeperatedCategory]
(
    @Codes VARCHAR(50)
)
RETURNS VARCHAR(5000)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Categories VARCHAR(5000)

    SELECT @Categories= STUFF
                        (
                                (SELECT ',' + convert(varchar(10), Name, 120)
                                FROM Category
                                WHERE Code IN (SELECT Id FROM [dbo].[fnSplit] (@Codes,',') )
                                ORDER BY Code
                                FOR XML PATH (''))
                        , 1, 1, '') 

    RETURN @Categories

END

AND Last:

SELECT
    Tenant,
    Code,
    (SELECT [dbo].[GetCommaSeperatedCategory] (Code)) AS Name
FROM TblTenant

Collectives™ on Stack Overflow

SQL Query using inner join

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related