Cross Tab - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Cross Tab

Author

Topic

Riddlemd
Starting Member

4 Posts

Posted - 2005-01-03 : 13:04:46

Can anyone there help me convert the below code into a cross tab that Looks like the below:

Component Officers Enlisted
Active # #
AR # #
SMCR # #
IMA # #
IRR # #

 
SELECT DISTINCT
COUNT(CASE WHEN (DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '03000' AND '04802' OR 
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '80172' AND '80297' OR
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '81180' AND '82271' OR
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '83170' AND '84286' OR
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '85217' AND '85298' OR
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '87050' AND '87297' OR 
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE IN ('20016', '36005'))AND DBO.MARINE.SSN
LIKE '[0]%' AND COMPONENT_CODE IN ('11', 'C1', 'C2', 'B1', 'B2', 'B3', 'B4',
'K6') AND (DBO.MARINE.PRESENT_REPORTING_UNIT_CODE LIKE '[BCDEFGS10]%' OR 
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE = 'W51') THEN 1 END) AS 'ACTIVE',
COUNT(CASE WHEN DBO.reserve.TRAINING_CATEGORY_PAY_GROUP = 'Q'
AND DBO.MARINE_INFO.RESERVE_RECORD_STATUS_CODE NOT IN ('9', 'D') THEN 1 END) AS 'AR',
COUNT(CASE WHEN DBO.reserve.TRAINING_CATEGORY_PAY_GROUP = 'A' AND 
DBO.MARINE_INFO.RESERVE_RECORD_STATUS_CODE NOT IN ('9', 'D') THEN 1 END) AS 'SMCR',
COUNT(CASE WHEN DBO.reserve.TRAINING_CATEGORY_PAY_GROUP IN ('B')
AND DBO.MARINE_INFO.RESERVE_RECORD_STATUS_CODE NOT IN ('9', 'D') THEN 1 END) AS 'IMA',
COUNT(CASE WHEN DBO.reserve.TRAINING_CATEGORY_PAY_GROUP IN ('H', 'J', 'E')
AND DBO.MARINE_INFO.RESERVE_RECORD_STATUS_CODE NOT IN ('9', 'D') THEN 1 END) AS 'IRR',

SUM (CASE WHEN (DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '03000' AND '04802' OR 
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '80172' AND '80297' OR
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '81180' AND '82271' OR
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '83170' AND '84286' OR
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '85217' AND '85298' OR
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '87050' AND '87297' OR 
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE IN ('20016', '36005'))AND DBO.MARINE.SSN
LIKE '[0]%' AND COMPONENT_CODE IN ('11', 'C1', 'C2', 'B1', 'B2', 'B3', 'B4',
'K6') AND (DBO.MARINE.PRESENT_REPORTING_UNIT_CODE LIKE '[BCDEFGS10]%' OR 
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE = 'W51') THEN 1 END)+ SUM(CASE WHEN DBO.reserve.TRAINING_CATEGORY_PAY_GROUP = 'Q'
AND DBO.MARINE_INFO.RESERVE_RECORD_STATUS_CODE NOT IN ('9', 'D') THEN 1 END)+SUM(CASE WHEN DBO.reserve.TRAINING_CATEGORY_PAY_GROUP = 'A' AND 
DBO.MARINE_INFO.RESERVE_RECORD_STATUS_CODE NOT IN ('9', 'D') THEN 1 END)+ SUM(CASE WHEN DBO.reserve.TRAINING_CATEGORY_PAY_GROUP IN ('B')
AND DBO.MARINE_INFO.RESERVE_RECORD_STATUS_CODE NOT IN ('9', 'D') THEN 1 END)+ SUM(CASE WHEN DBO.reserve.TRAINING_CATEGORY_PAY_GROUP IN ('H', 'J', 'E')
AND DBO.MARINE_INFO.RESERVE_RECORD_STATUS_CODE NOT IN ('9', 'D') THEN 1 END) AS 'TOTAL',

COUNT(CASE WHEN DBO.reserve.TRAINING_CATEGORY_PAY_GROUP = 'A'
AND DBO.MARINE_INFO.RESERVE_RECORD_STATUS_CODE NOT IN ('9', 'D')
AND DBO.MARINE.COMPONENT_CODE = 'KM' THEN 1 END) AS 'CURRENTLY ACTIVATED SMCR',

COUNT(CASE WHEN DBO.reserve.TRAINING_CATEGORY_PAY_GROUP IN ('H', 'J', 'E')
AND DBO.MARINE_INFO.RESERVE_RECORD_STATUS_CODE NOT IN ('9', 'D')
AND DBO.MARINE.COMPONENT_CODE = 'KM' THEN 1 END) AS 'CURRENTLY ACTIVATED IRR'

FROM         dbo.MARINE INNER JOIN
                      dbo.reserve ON dbo.MARINE.SSN = dbo.reserve.SSN INNER JOIN
                      dbo.MARINE_INFO ON dbo.MARINE.SSN = dbo.MARINE_INFO.SSN

Maj Riddle
Data cell OIC

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-03 : 13:15:46

you should really look into storing all this data in tables, instead of hard-coding values in your case statements. That thing is ugly. All of those "Where somethiing in ...." or "something LIKE ..." or "Something between ..." case statements should be LEFT OUTER JOINS to tables containing the parameters and then what value to return if they match. For example, the condition "DBO.MARINE_INFO.RESERVE_RECORD_STATUS_CODE NOT IN ('9', 'D')" is listed over and over -- you just be joining to a "Status" table that tells you how to handle the values of 9 and D, not doing this over and over in a CASE statement.

Either way, there is no indication at all in the above query of what the difference between Officer and Enlisted is.

My advice -- start over. with data stored properly, this whole thing can probably be a 10-15 line SELECT statement.

- Jeff

Riddlemd
Starting Member

4 Posts

Posted - 2005-01-03 : 13:26:43

I am fairly new at this... I don't control what is stored in the Tables. I am only allowed to create views. Is there a way?

Maj Riddle
Data cell OIC

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-03 : 14:15:12

Either way, there is no indication at all in the above query of what the difference between Officer and Enlisted is.

- Jeff

Riddlemd
Starting Member

4 Posts

Posted - 2005-01-03 : 14:18:28

I'm sorry I copied the wrong query. Please look at this one: Present_Grade_Code is the deliniating field..

Thanks,
Mike

SELECT DISTINCT
COUNT(CASE WHEN (DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '03000' AND '04802' OR
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '80172' AND '80297' OR
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '81180' AND '82271' OR
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '83170' AND '84286' OR
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '85217' AND '85298' OR
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '87050' AND '87297' OR
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE IN ('20016', '36005'))AND DBO.MARINE.SSN
LIKE '[0]%' AND COMPONENT_CODE IN ('11', 'C1', 'C2', 'B1', 'B2', 'B3', 'B4',
'K6') AND (DBO.MARINE.PRESENT_REPORTING_UNIT_CODE LIKE '[BCDEFGS10]%' OR
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE = 'W51') AND PRESENT_GRADE_CODE LIKE '[WO]%' THEN 1 END) AS 'ACTIVE_Officer',
COUNT(CASE WHEN (DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '03000' AND '04802' OR
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '80172' AND '80297' OR
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '81180' AND '82271' OR
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '83170' AND '84286' OR
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '85217' AND '85298' OR
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE BETWEEN '87050' AND '87297' OR
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE IN ('20016', '36005'))AND DBO.MARINE.SSN
LIKE '[0]%' AND COMPONENT_CODE IN ('11', 'C1', 'C2', 'B1', 'B2', 'B3', 'B4',
'K6') AND (DBO.MARINE.PRESENT_REPORTING_UNIT_CODE LIKE '[BCDEFGS10]%' OR
DBO.MARINE.PRESENT_REPORTING_UNIT_CODE = 'W51') AND PRESENT_GRADE_CODE LIKE '[E]%' THEN 1 END) AS 'ACTIVE_Enlisted',

COUNT(CASE WHEN DBO.reserve.TRAINING_CATEGORY_PAY_GROUP = 'Q'
AND DBO.MARINE_INFO.RESERVE_RECORD_STATUS_CODE NOT IN ('9', 'D') AND PRESENT_GRADE_CODE LIKE '[WO]%' THEN 1 END) AS 'AR_Officer',
COUNT(CASE WHEN DBO.reserve.TRAINING_CATEGORY_PAY_GROUP = 'Q'
AND DBO.MARINE_INFO.RESERVE_RECORD_STATUS_CODE NOT IN ('9', 'D') AND PRESENT_GRADE_CODE LIKE '[E]%' THEN 1 END) AS 'AR_Enlisted',

COUNT(CASE WHEN DBO.reserve.TRAINING_CATEGORY_PAY_GROUP = 'A' AND
DBO.MARINE_INFO.RESERVE_RECORD_STATUS_CODE NOT IN ('9', 'D') AND PRESENT_GRADE_CODE LIKE '[WO]%' THEN 1 END) AS 'SMCR_Officer',
COUNT(CASE WHEN DBO.reserve.TRAINING_CATEGORY_PAY_GROUP = 'A' AND
DBO.MARINE_INFO.RESERVE_RECORD_STATUS_CODE NOT IN ('9', 'D') AND PRESENT_GRADE_CODE LIKE '[E]%' THEN 1 END) AS 'SMCR Enlisted',

COUNT(CASE WHEN DBO.reserve.TRAINING_CATEGORY_PAY_GROUP IN ('B')
AND DBO.MARINE_INFO.RESERVE_RECORD_STATUS_CODE NOT IN ('9', 'D') AND PRESENT_GRADE_CODE LIKE '[WO]%'THEN 1 END) AS 'IMA_Officer',
COUNT(CASE WHEN DBO.reserve.TRAINING_CATEGORY_PAY_GROUP IN ('B')
AND DBO.MARINE_INFO.RESERVE_RECORD_STATUS_CODE NOT IN ('9', 'D') AND PRESENT_GRADE_CODE LIKE '[E]%'THEN 1 END) AS 'IMA_Enlisted',

COUNT(CASE WHEN DBO.reserve.TRAINING_CATEGORY_PAY_GROUP IN ('H', 'J', 'E')
AND DBO.MARINE_INFO.RESERVE_RECORD_STATUS_CODE NOT IN ('9', 'D') AND PRESENT_GRADE_CODE LIKE '[WO]%' THEN 1 END) AS 'IRR_Officer',
COUNT(CASE WHEN DBO.reserve.TRAINING_CATEGORY_PAY_GROUP IN ('H', 'J', 'E')
AND DBO.MARINE_INFO.RESERVE_RECORD_STATUS_CODE NOT IN ('9', 'D') AND PRESENT_GRADE_CODE LIKE '[E]%' THEN 1 END) AS 'IRR_Enlisted'

FROM dbo.MARINE INNER JOIN
dbo.reserve ON dbo.MARINE.SSN = dbo.reserve.SSN INNER JOIN
dbo.MARINE_INFO ON dbo.MARINE.SSN = dbo.MARINE_INFO.SSN

Maj Riddle
Data cell OIC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources