Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 10110 + 11110

Author  Topic 

lexiz
Starting Member

17 Posts
Posted - 2010-03-01 : 15:37:11
Hi , I have a column that is made up of a binary string of char (5)

each characters should only be 0 or 1

what I need to be able to do is take a list of these, query them so i get a final result that shows where each character position is 1, so the simple example below demonstates this:

1 0 0 1 1
1 0 1 1 0
1 0 0 0 0
-----------
1 0 1 1 1 And this should be the result
-----------


I also need this to be code that works in a view -I am sure it would be easy to produce something a bit more complex in a stored proc

I do have a basic idea/way to produce this, but its far from ideal. what I have done is to prefix the char(4) with a 1 making it char(5) but this means i can convert the char to a int- sum the column and any didget that isnt a 0 should then be a 1 so the above example would look like

1 0 0 1 1
1 0 1 1 0
1 0 0 0 0
-----------
3 0 1 2 1 And this is the result
-----------

I know some of you will be rolling your eyes at this point, and yes I am aware that it will probably all go wrong if I am suming more than 9 records, but it might be posible for me to limit my application so is no more than 9 records for this part of the functionality....

I would rather not, I think / hope someone can suggest a better solution

many thanks in advance for any help ;)

LoztInSpace
Aged Yak Warrior

940 Posts
Posted - 2010-03-01 : 21:16:57
What you suggest is easy enough and your solution works ok I suppose.
I don't think you've given enough info though. Storing things like this is rarely correct (as you are discovering) but as you haven't shared with us what you are actually trying to, you're not really likely to get any improved solutions.
(this seems to be the only response I write these days!).
Go to Top of Page

lexiz
Starting Member

17 Posts
Posted - 2010-03-02 : 04:38:56
Thank for replying. i would have given more detail but all in all the system is pretty complex, but hear goes I will give it a try to explain with out going into to much detail.

I made the example simpler to but in reality each string I am using is char(15). each set of numbers shows permissions on an object in my system 0 being false 1 being true. each place place in the string represents the type of permission, (ie the 2nd is read only).Objects can be something like something like a report.
Each report(object) can have groups permissions assigned to it by group. which is hthe stage I am at above, i have a set of or groups (that the user belongs to) with permissions to an object, I then need to extract the highest posible privilage the user has from that set of groups. so e might only have read only in some but full access in others.

I tried t save you from the detail of the application, I am not sure how much ths extra detail I have supplied will help. but who knows...
Go to Top of Page

lexiz
Starting Member

17 Posts
Posted - 2010-03-02 : 04:45:39
actually, a bit more info that might help. the application runs over the web.So I am trying to make as few calls to the database as posible. Having the highest permission for the user in a veiw with a set of objects allows me to easily give the front end gui the information it needs, without it querying to find out what the permissions are on each object (the front end can be supplied with a list of objects, its much more useful having the permissions string neext to in in the list).

And its the Gui that takes the permissions string appart to work out what is allowable etc.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts
Posted - 2010-03-02 : 09:08:02
Try defining each group as a row with 15 columns in it representing each permission. Then select all the max(permission1), max(permission2) etc from each group where the user has membership of.

So your tables are:
User (name etc)
Group (ID, 15 permission flags)
UserToGroup(userID, GroupID)

join them all up and MAX each flag.

You could also do something funky with a permission table, groups to permissions and do a similar thing with a pivot but I think the first solution will do.
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts
Posted - 2010-03-02 : 16:22:10
You are trying to OR the permissions together. Since this is 2008 you could implement an aggregate function that OR'd together the rows for vone user.

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2010-03-02 : 18:42:31
Are you using a string value so you can have more than 63 possible bit values? If not, you might look into using someting like bit masking. For example to get powers of two you can use the POWER functon:
SELECT 
	POWER(2, 0),
	POWER(2, 1),
	POWER(2, 2),
	POWER(2, 3)

Then you can use that to build values and check equality:
DECLARE @Foo TABLE (ID INT IDENTITY(1,1), Val INT)

INSERT @Foo
SELECT POWER(2,0) + POWER (2,3) -- 1001
UNION ALL SELECT POWER(2,0) + POWER(2,1) + POWER(2,2) -- 0111
UNION ALL SELECT POWER(2,0) -- 0001
UNION ALL SELECT POWER(2,1) + POWER(2,2) -- 0110
UNION ALL SELECT POWER(2,1) + POWER(2,3) -- 1010

SELECT *
FROM @Foo
WHERE (Val & POWER(2,0)) = POWER(2,0)
Go to Top of Page

lexiz
Starting Member

17 Posts
Posted - 2010-03-03 : 07:32:26
Thanks for all the responses,

LoztInSpace...
strangely enough I didnt realy consider having each didget in the string in a different, its not a solution I like but is definatly better than I have so far. I could just implement this in the database and still pass a string char to the front end and back.

Bustaz Kool.....
I am basically trying to find the highest value diget(should be either 1 or 0)for each position in the char from a set of values in a query.
I did try and look up the OR that you were refering to in 2008, but couldnt find any mention of it, or it being available in the group by drop down combo, in sql designer.

Lamprey...
I think you might have fully understood what I am trying to do, or you did and I dont understand your answer fully(with i think is also true)
Go to Top of Page

David Singleton
Starting Member

35 Posts
Posted - 2010-03-03 : 08:58:17
quote:
Originally posted by lexiz


I know some of you will be rolling your eyes at this point,


Wow you must be psychic that you knew that.

Basically if you go this path you will for ever be asking new questions for new problems as they arise.

I personally would go for the solution suggested above of adding a new field for each permissions, rather than trying to be fancy and save a few bytes of extremely cheap disk space.

David Singleton

Microsoft MVP Dynamics NAV
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2010-03-03 : 18:25:50
[code]DECLARE @Sample TABLE
(
Data CHAR(5)
)

INSERT @Sample
VALUES ('10011'),
('10110'),
('10000')

;WITH cteSource(Data, Position, Digit)
AS (
SELECT s.Data,
f.Position,
f.Digit
FROM @Sample AS s
CROSS APPLY (
SELECT v.Number,
CAST(SUBSTRING(s.Data, v.Number, 1) AS INT) AS Digit
FROM master..spt_values AS v
WHERE v.Number BETWEEN 1 AND LEN(s.Data)
AND v.Type = 'P'
) AS f(Position, Digit)
), cteAggregation(Position, Case1, Case2)
AS (
SELECT Position,
MAX(Digit) AS Case1,
SUM(Digit) AS Case2
FROM cteSource
GROUP BY Position
)
SELECT 1 AS theCase,
CAST(c1.s AS CHAR(5)) AS theResult
FROM (
SELECT '' + CAST(Case1 AS CHAR(1))
FROM cteAggregation
ORDER BY Position
FOR XML PATH('')
) AS c1(s)

UNION ALL

SELECT 2 AS theCase,
CAST(c2.s AS CHAR(5)) AS theResult
FROM (
SELECT '' + CAST(Case2 AS CHAR(1))
FROM cteAggregation
ORDER BY Position
FOR XML PATH('')
) AS c2(s)[/code]

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts
Posted - 2010-03-03 : 19:53:23
[code]DECLARE @Sample TABLE
(
Data CHAR(5)
)

INSERT @Sample
VALUES ('10011'),
('10110'),
('10000')

SELECT 1 AS theCase,
MAX(SUBSTRING(Data, 1, 1))+
MAX(SUBSTRING(Data, 2, 1))+
MAX(SUBSTRING(Data, 3, 1))+
MAX(SUBSTRING(Data, 4, 1))+
MAX(SUBSTRING(Data, 5, 1)) AS theResult
FROM @Sample
UNION ALL
SELECT 2,
CAST(SUM(CASE WHEN SUBSTRING(Data, 1, 1) = 1 THEN 1 ELSE 0 END) AS CHAR(1))+
CAST(SUM(CASE WHEN SUBSTRING(Data, 2, 1) = 1 THEN 1 ELSE 0 END) AS CHAR(1))+
CAST(SUM(CASE WHEN SUBSTRING(Data, 3, 1) = 1 THEN 1 ELSE 0 END) AS CHAR(1))+
CAST(SUM(CASE WHEN SUBSTRING(Data, 4, 1) = 1 THEN 1 ELSE 0 END) AS CHAR(1))+
CAST(SUM(CASE WHEN SUBSTRING(Data, 5, 1) = 1 THEN 1 ELSE 0 END) AS CHAR(1))
FROM @Sample

/*
theCase theResult
----------- ---------
1 10111
2 30121
*/

[/code]
Go to Top of Page

lexiz
Starting Member

17 Posts
Posted - 2010-03-04 : 08:06:37
Thanks so much for the replies, certainly a lotof helpful people here.

David S. is probably correct that I should split the string into columnsinthe db, I have been trying to think why I mad ethe decission to put it al in one string to start witth (i thinkit might have been something to do with the GUI), but anyway.

If I do keep it all as 1 colmun then I can use part of ms65g solutin

SELECT 1 AS theCase,
MAX(SUBSTRING(Data, 1, 1))+
MAX(SUBSTRING(Data, 2, 1))+
MAX(SUBSTRING(Data, 3, 1))+
MAX(SUBSTRING(Data, 4, 1))+
MAX(SUBSTRING(Data, 5, 1)) AS theResult
FROM @Sample

I just have to add some grouping and i think it should work well in a view (i hope anyway)
thanks again all, for your time.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site