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: Monday, July 7, 2014 7:01 AM
Points: 20, Visits: 47
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 @ 9:56 AM
Points: 7,120, Visits: 13,491
-- 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:25 PM
Points: 3,333, Visits: 7,193
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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