I have a query that returns a result set that looks like:
Userid Perms
testid 1=0 2=1 45=2 234=1
testid 1=1 2=2 3=1 100=0
testid2 1=2 2=1 3=2 100=2
Each userid can have mutliple records. I need to collapse these into one for each user with the individual perms "CASE"ed into 1 value. The number prior to the = sign is the permission number and the number following the = sign is the permission value which needs to be merged.
I've created a query that parses the records and returns a result set with a record for each user and permission:
Userid PermVal
testid 1=0
testid 2=1
testid 45=2...
testid2 1=2
testid2 2=1
The problem is that I end up with over a half million records that need to be concatenated back into the original
format listed at the top of this post. I am trying to do in a set based manner (there is already a cursor based
solution that is being used). Does anyone have any ideas on how to handle this? Or ideas on how to *better* handle this? I
've included my current query below. The #Nums table has 1 integer column and is populated with the values of 1 to 8000.
SELECT userid, Permno + Permval FROM
(SELECT TOP 100 PERCENT userid, Permno = SUBSTRING(perms, num + 1, CHARINDEX('=', perms, num) - num),
Permval = CASE
WHEN MIN(SUBSTRING(perms, CHARINDEX('=', perms, num) + 1, 1)) = 0 THEN '0'
WHEN MAX(SUBSTRING(perms, CHARINDEX('=', perms, num) + 1, 1)) = 0 THEN '2'
ELSE '1'
END
FROM #Nums JOIN #PermsTab ON SUBSTRING(perms, num, 1) = ' '
WHERE num < DATALENGTH(perms)
GROUP BY userid, SUBSTRING(perms, num + 1, CHARINDEX('=', perms, num) - num)
ORDER BY userid, SUBSTRING(perms, num + 1, CHARINDEX('=', perms, num) - num)) x
I would really appreciate any suggestions even if it means a total rewrite.
Thanks,
George
P.S. Sorry for the horrid formatting of the message. I can't seem figure out how the formatting on this site works.