Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Compare Comma seprated values of two table in sql server 2005 Expand / Collapse
Author
Message
Posted Friday, May 16, 2014 12:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 12:06 AM
Points: 20, Visits: 49
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....
Post #1571614
Posted Friday, May 16, 2014 4:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:49 AM
Points: 6,872, Visits: 14,184
-- 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/


“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
Exploring Recursive CTEs by Example Dwain Camps
Post #1571665
Posted Friday, May 16, 2014 8:25 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:29 AM
Points: 3,926, Visits: 8,922
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1571751
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse