Post reply

Compare Comma seprated values of two table in sql server 2005

  • May 16, 2014 at 12:40 am

    #151349

    I have two Tables

    Table A and Table B

    In Table A there are two column UserId(int) and PermissionId(varchar(Max))[save values in comma separated]

    In Table B there are two column UserId(int) and UserLevelRights(varchar(Max))[save values in comma separated]

    Now i have to compare TableA.PermissionId and TableB.UserLevelRights

    and Get those value which are common in both table.

    any suggestions....

  • May 16, 2014 at 4:59 am

    #1714140

    -- set up some sample data

    DROP TABLE #t1

    CREATE TABLE #t1 (Table1ID INT IDENTITY(1,1) PRIMARY KEY, DenormalisedString VARCHAR(8000))

    INSERT INTO #t1 (DenormalisedString) VALUES ('Red,Green,Blue'),('Red,Green'),('Red,Blue'),('Green,Blue')

    DROP TABLE #t2

    CREATE TABLE #t2 (Table2ID INT IDENTITY(1,1) PRIMARY KEY, DenormalisedString VARCHAR(8000))

    INSERT INTO #t2 (DenormalisedString) VALUES ('Red,White,Blue'),('Red,White'),('Red,Blue'),('White,Blue')

    -- For testing this query

    SELECT

    t1.Table1ID, t1.DenormalisedString,

    t2.Table2ID, t2.DenormalisedString,

    CommonItems = STUFF(y.CommonItems,1,1,'')

    FROM #t1 t1

    CROSS JOIN #t2 t2

    CROSS APPLY ( -- y

    -- Resolve out the items and match up,

    -- then pivot the matching items back into a comma-delimited string

    SELECT ','+x2.Item

    FROM dbo.DelimitedSplit8K(t2.DenormalisedString,',') x2

    CROSS APPLY dbo.DelimitedSplit8K(t1.DenormalisedString,',') x1

    WHERE x1.Item = x2.Item

    ORDER BY x2.ItemNumber

    FOR XML PATH('')

    ) y (CommonItems)

    ORDER BY t1.Table1ID, t2.Table2ID

    For function dbo.DelimitedSplit8K, see http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • May 16, 2014 at 8:25 am

    #1714207

    You should be aware that if you use a varchar(MAX) column a input to the DelimitedSplit8K function, you can have silent truncation.

    Other than that, it's a great function.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply