How do I get stats on this special "binary" field?

Question

Post reply

How do I get stats on this special "binary" field?

halifaxdal

SSCoach

Points: 19741
More actions
March 14, 2014 at 3:18 pm

#396425

I have a table with a field of varchar(15) and its content is like: 111000101010100 where each digit correspondent to another table called Categories, if the digit is 1, it means the record is classified as Category 1, and each record can be classified as multiple categories, that's why there could be more than one 1s in the field.
Now what I am asked to do is simple in terms of business requirement: to find out from the table, how many records are classified to each category?
For your convenience, I have the script to create a simplified table and the script to generate some sample data:
Thank you very much in advance.
CREATE TABLE [dbo].[Document](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Division] [int] NULL,
[Cat] [varchar](15) NULL,
CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Script to generate data:
insert into document (Division, Cat) Values (1, '111000101010100')
insert into document (Division, Cat) Values (1, '111000101010101')
insert into document (Division, Cat) Values (1, '101000101011010')
insert into document (Division, Cat) Values (1, '011000101010101')
insert into document (Division, Cat) Values (1, '101000100010000')
insert into document (Division, Cat) Values (2, '110000101010100')
insert into document (Division, Cat) Values (2, '110001101010100')
insert into document (Division, Cat) Values (2, '011010101010100')
insert into document (Division, Cat) Values (2, '011000101010100')
insert into document (Division, Cat) Values (3, '111000101010100')
insert into document (Division, Cat) Values (3, '111010101010101')
insert into document (Division, Cat) Values (3, '101010101011000')
insert into document (Division, Cat) Values (3, '011000101000001')
insert into document (Division, Cat) Values (3, '101000100011111')
Category table and data:
CREATE TABLE [dbo].Categories(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Cat] [varchar](20) NULL,
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
insert into Categories (Cat) Values ('Cat1')
insert into Categories (Cat) Values ('Cat2')
insert into Categories (Cat) Values ('Cat3')
For the above given sample data, I am expecting a result like:
Division Cat Total
1 Cat1 12
1 Cat2 1
1 Cat3 2
1 Cat4 3
1 Cat5 0
1 Cat6 1
1 Cat7 12
1 Cat8 1
1 Cat9 2
1 Cat10 3
1 Cat11 0
1 Cat12 1
1 Cat13 2
1 Cat14 3
1 Cat15 2
2 Cat1 12
2 Cat2 1
2 Cat3 2
2 Cat4 3
2 Cat5 0
2 Cat6 1
2 Cat7 12
2 Cat8 1
2 Cat9 2
2 Cat10 3
2 Cat11 0
2 Cat12 1
2 Cat13 2
2 Cat14 3
2 Cat15 2

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

If I understand correctly, you might be looking for something like this:

SELECT Division

,c2.Cat

,SUM( CAST( c.Value AS int)) Total

FROM #Document

CROSS APPLY( VALUES(1, SUBSTRING(Cat, 1, 1)),

(2, SUBSTRING(Cat, 2, 1)),

(3, SUBSTRING(Cat, 3, 1)),

(4, SUBSTRING(Cat, 4, 1)),

(5, SUBSTRING(Cat, 5, 1)),

(6, SUBSTRING(Cat, 6, 1)),

(7, SUBSTRING(Cat, 7, 1)),

(8, SUBSTRING(Cat, 8, 1)),

(9, SUBSTRING(Cat, 9, 1)),

(10, SUBSTRING(Cat, 10, 1)),

(11, SUBSTRING(Cat, 11, 1)),

(12, SUBSTRING(Cat, 12, 1)),

(13, SUBSTRING(Cat, 13, 1)),

(14, SUBSTRING(Cat, 14, 1)),

(15, SUBSTRING(Cat, 15, 1))) c(Cat, Value)

JOIN #Categories c2 ON c.Cat = c2.ID

GROUP BY Division, c2.Cat, c2.ID

ORDER BY Division, c2.ID

However, you should be looking forward to normalize the data. 😀

Luis C.
General Disclaimer:
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?

How to post data/code on a forum to get the best help: Option 1 / Option 2

halifaxdal SSCoach Points: 19741 More actions · Answer 2

halifaxdal

SSCoach

Points: 19741

March 14, 2014 at 4:40 pm

#1697355

Beautifully working!!! Thank you so much.

halifaxdal SSCoach Points: 19741 More actions · Answer 3

Too bad! Only beautifully working on my local 2k8, when I run it on server 2k5, it fails with the term "VALUES"

CROSS APPLY( VALUES(1, SUBSTRING(d.Category, 1, 1)),

is VALUES new in 2k8? how to get around with it in 2k5?

Thanks again for the help.

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 4

halifaxdal (3/17/2014)
Too bad! Only beautifully working on my local 2k8, when I run it on server 2k5, it fails with the term "VALUES"
CROSS APPLY( VALUES(1, SUBSTRING(d.Category, 1, 1)),
is VALUES new in 2k8? how to get around with it in 2k5?
Thanks again for the help.

I'd recommend that you stop posting 2005 questions on a 2008 forum.

I beleive you can just change the Values() to a SELECT for each row and include a UNION ALL for each and Bob's your uncle. Don't forget to take out the parenthesis for Values().

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

halifaxdal SSCoach Points: 19741 More actions · Answer 5

Jeff Moden (3/17/2014)
halifaxdal (3/17/2014)
Too bad! Only beautifully working on my local 2k8, when I run it on server 2k5, it fails with the term "VALUES"
CROSS APPLY( VALUES(1, SUBSTRING(d.Category, 1, 1)),
is VALUES new in 2k8? how to get around with it in 2k5?
Thanks again for the help.
I'd recommend that you stop posting 2005 questions on a 2008 forum.

My local is 2k8, the new issue happens when I deploy it to 2k5, should I re-open the same question in 2k5? I am afraid it would be banned by moderator for posting same question on different forum.

I beleive you can just change the Values() to a SELECT for each row and include a UNION ALL for each and Bob's your uncle. Don't forget to take out the parenthesis for Values().

Thank you for the trick.