May 16, 2014 at 12:40 am
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
-- 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]
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
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply