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, Sep 22 '16 at 12:28
I do have a split function already, but dont know how to incorporate it to the code @SandipPatel — rickyProgrammer, Sep 22 '16 at 12:30
Create one function and pass (1,2) and it return you (Food,Non-Food) — Sandip - Frontend Developer, Sep 22 '16 at 12:30
i have no other idea on how do it so i resort to comma separated items way of doing it @jarlh — rickyProgrammer, Sep 22 '16 at 12:30
You would need to create a function that returns inputs a VARCHAR and returns a VARCHAR. The function would need to split the input, join your first table based on the split input, then STUFF the joined values into a VARCHAR. — ZLK, Sep 22 '16 at 12:32
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, Sep 22 '16 at 12:34
Ricky, I would seriously consider changing the schema to be normalized, this is an issue that will only haunt you forever and you having to do workarounds of this kind that will impact performance in the long term. — LogicalMan, Sep 22 '16 at 12:51

neer · Accepted Answer · 2016-09-22T13:03:42.277

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 '16 at 13:03

answered Sep 22 '16 at 12:52

neer

4,031
6
20
34

Nice, but if we add `(10, 'Something')` in `@CategoryTable` and `('Tenant3', '10')`. Your query will return `Tenant3 | 10 | Food,Something` :( – gofr1 Sep 22 '16 at 12:56
HI @NEER I am trying your code but it is giving me this error: Error converting data type varchar to numeric. – rickyProgrammer Sep 26 '16 at 05:56
Can you show column type of table? I can edit answer then. – neer Sep 26 '16 at 05:57
@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 – rickyProgrammer Sep 26 '16 at 06:20
Whats type of column? – neer Sep 26 '16 at 06:24

score 0 · Answer 2 · answered Sep 22 '16 at 12:45

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.

score 0 · Answer 3 · answered Sep 22 '16 at 12:47

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

SQL Query using inner join

3 Answers3