how to : determine something based on several colomns value.

Question

how to : determine something based on several colomns value.

dubem1-878067

Ten Centuries

Points: 1044
More actions
July 26, 2013 at 12:05 pm

#276369

I will try to explain the best I can the situation I am facing
I have a table with 12 'bit columns' each of them reprensent a potential specific problematics. In the web form, user check the problematic he is facing, they are separeted in 3 sections of 4 checkbox, if he checks one checkbox in a section, the checkbox is the 2 others section gray out
so
bit columns 1 to 4 refers to category 1
bit columns 5 to 8 refers to category 2
bit columns 9 to 12 refers to category 3
I need to build a query from that table to fill a report
row 1, bit column 3 is set to true : I need to return -> Category 1
row 2, bit column 5 is set to true : I need to return -> Category 2
How can I manage this, with a CASE?
thanks

Viewing 3 posts - 1 through 2 (of 2 total)

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

dubem1-878067 (7/26/2013)
I will try to explain the best I can the situation I am facing
I have a table with 12 'bit columns' each of them reprensent a potential specific problematics. In the web form, user check the problematic he is facing, they are separeted in 3 sections of 4 checkbox, if he checks one checkbox in a section, the checkbox is the 2 others section gray out
so
bit columns 1 to 4 refers to category 1
bit columns 5 to 8 refers to category 2
bit columns 9 to 12 refers to category 3
I need to build a query from that table to fill a report
row 1, bit column 3 is set to true : I need to return -> Category 1
row 2, bit column 5 is set to true : I need to return -> Category 2
How can I manage this, with a CASE?
thanks

You description is entirely too vague. Remember we don't know your project, we can't see your screen, we have no idea what your tables look and we have no concept of what you are trying to do.

In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 2

dubem1-878067 (7/26/2013)
I will try to explain the best I can the situation I am facing
I have a table with 12 'bit columns' each of them reprensent a potential specific problematics. In the web form, user check the problematic he is facing, they are separeted in 3 sections of 4 checkbox, if he checks one checkbox in a section, the checkbox is the 2 others section gray out
so
bit columns 1 to 4 refers to category 1
bit columns 5 to 8 refers to category 2
bit columns 9 to 12 refers to category 3
I need to build a query from that table to fill a report
row 1, bit column 3 is set to true : I need to return -> Category 1
row 2, bit column 5 is set to true : I need to return -> Category 2
How can I manage this, with a CASE?
thanks

I can guess at this because I did a project just like it not too long ago. In my case the table was handed to me with over 160 Y/N columns! What a nightmare! The main problem I think you need to deal with is to normalize your data. After that writing the queries is easy. I've created a new schema for this and some sample data. Once this is set up you'll have much better control and performance with your queries.

-- This whole section is just to set up the schema and insert sample data for testing

IF OBJECT_ID('tempdb..#SampleQuestions') IS NOT NULL

DROP TABLE #SampleQuestions;

IF OBJECT_ID('tempdb..#SampleCategories') IS NOT NULL

DROP TABLE #SampleCategories;

IF OBJECT_ID('tempdb..#SampleResponses') IS NOT NULL

DROP TABLE #SampleResponses;

CREATE TABLE #SampleCategories

(

[CatID] [int] IDENTITY(1,1) NOT NULL,

[Category] [varchar](100) NOT NULL,

PRIMARY KEY CLUSTERED ([CatID],[Category])

)

CREATE TABLE #SampleQuestions

(

[QuestionID] [int] IDENTITY(1,1) NOT NULL,

[Question] [varchar](100) NOT NULL,

[CatID] [int] NULL,

PRIMARY KEY CLUSTERED ([QuestionID],[Question])

)

CREATE TABLE #SampleResponses

(

[ResponseID] [int] IDENTITY(1,1) NOT NULL,

[QuestionID] [int] NOT NULL,

[RespondentName] VARCHAR(30),

[Response] [bit] NOT NULL DEFAULT 0,

PRIMARY KEY CLUSTERED ([ResponseID],[QuestionID],[Response])

)

Create some sample categories

;WITH InsertSampleCategories ([Category])

AS (

SELECT 'Category 1' UNION ALL

SELECT 'Category 2' UNION ALL

SELECT 'Category 3' UNION ALL

SELECT 'Category 4'

)

INSERT INTO #SampleCategories

SELECT Category FROM InsertSampleCategories

SELECT * FROM #SampleCategories

Create some sample questions

;WITH InsertSampleQuestions ([Question], [CatID])

AS (

SELECT 'Question 1','1' UNION ALL

SELECT 'Question 2','2' UNION ALL

SELECT 'Question 3','1' UNION ALL

SELECT 'Question 4','1' UNION ALL

SELECT 'Question 5','2' UNION ALL

SELECT 'Question 6','2' UNION ALL

SELECT 'Question 7','2' UNION ALL

SELECT 'Question 8','2' UNION ALL

SELECT 'Question 9','1' UNION ALL

SELECT 'Question 10','3' UNION ALL

SELECT 'Question 11','3' UNION ALL

SELECT 'Question 12','3'

)

INSERT INTO #SampleQuestions

SELECT [Question], [CatID] FROM InsertSampleQuestions

SELECT * FROM #SampleQuestions

Create some sample responses

;WITH InsertSampleResponses ([QuestionID], [RespondentName], [Response])

AS (

SELECT 1,'John',1 UNION ALL

SELECT 2,'John',1 UNION ALL

SELECT 3,'John',0 UNION ALL

SELECT 4,'John',1 UNION ALL

SELECT 5,'John',0 UNION ALL

SELECT 6,'John',1 UNION ALL

SELECT 7,'John',1 UNION ALL

SELECT 8,'John',0 UNION ALL

SELECT 9,'John',0 UNION ALL

SELECT 10,'John',1 UNION ALL

SELECT 11,'John',1 UNION ALL

SELECT 12,'John',1 UNION ALL

SELECT 1,'Tom',0 UNION ALL

SELECT 2,'Tom',1 UNION ALL

SELECT 3,'Tom',0 UNION ALL

SELECT 4,'Tom',1 UNION ALL

SELECT 5,'Tom',1 UNION ALL

SELECT 6,'Tom',1 UNION ALL

SELECT 7,'Tom',1 UNION ALL

SELECT 8,'Tom',0 UNION ALL

SELECT 9,'Tom',1 UNION ALL

SELECT 10,'Tom',1 UNION ALL

SELECT 11,'Tom',1 UNION ALL

SELECT 12,'Tom',1 UNION ALL

SELECT 1,'Mary',0 UNION ALL

SELECT 2,'Mary',1 UNION ALL

SELECT 3,'Mary',0 UNION ALL

SELECT 4,'Mary',1 UNION ALL

SELECT 5,'Mary',1 UNION ALL

SELECT 6,'Mary',1 UNION ALL

SELECT 7,'Mary',1 UNION ALL

SELECT 8,'Mary',0 UNION ALL

SELECT 9,'Mary',1 UNION ALL

SELECT 10,'Mary',1 UNION ALL

SELECT 11,'Mary',1 UNION ALL

SELECT 12,'Mary',1

)

INSERT INTO #SampleResponses

SELECT [QuestionID], [RespondentName], [Response] FROM InsertSampleResponses

SELECT * FROM #SampleResponses

Now we can add some queries. No case statements needed.

SELECT

q.Question

,r.RespondentName

,r.Response

,c.Category

FROM

#SampleResponses AS r

INNER JOIN

#SampleQuestions AS q

ON r.QuestionID = q.QuestionID

INNER JOIN

#SampleCategories AS c

ON q.CatID = c.CatID

ORDER BY

r.QuestionID,

c.Category,

r.RespondentName

SELECT

q.Question

,r.RespondentName

,r.Response

,c.Category

FROM

#SampleResponses AS r

INNER JOIN

#SampleQuestions AS q

ON r.QuestionID = q.QuestionID

INNER JOIN

#SampleCategories AS c

ON q.CatID = c.CatID

WHERE

r.Response = 1