[dba-SQLServer] SQL "CROSS TAB"

jeffrey.demulling at usbank.com jeffrey.demulling at usbank.com
Wed Jan 19 15:00:01 CST 2005


Using the sql below I need to create a "cross tab" that looks like this:

Office ;TR Category ;0-30;31-60;61;90;91+
Office1;Category1;0;0;1;5
Office1;Category3;1;0;4;0
Office2;Category5;7;0;6;10

Any and all help would be appreciated as I am at a complete loss.  Thank
you in advance.


SELECT
tblRegions.Name AS RegionName,
tblOffices.Name AS OfficeName,
tblNames_1.Name AS [AM Name],
tblNames_2.Name AS [NS Name],
tblNames.Name AS [ASU Name],
tblProductTypes.Name AS ProductCodeName,
tblProductTypes.[TR Category],

[Aging Category - CDR Setup Status] = CASE WHEN  [tblDeals].[Deal Status]
<> 3 THEN
'N/A'
Else
CASE WHEN [tblDeals].[CDR Setup Done] IS NOT NULL THEN
'N/A'
Else
CASE WHEN [tblDeals].[CDR Setup Due] IS NULL AND [tblDeals].[CDR Setup
Done] IS NULL THEN
'N/A'
Else
CASE WHEN [tblDeals].[CDR Setup Due] IS NOT NULL AND [tblDeals].[CDR Setup
Done] IS NULL THEN
CASE WHEN DATEDIFF(DAY,[tblDeals].[Actual Close Date], GETDATE())<31 THEN
'0-30'
WHEN DATEDIFF(DAY,[tblDeals].[Actual Close Date], GETDATE())<61 THEN
'31-60'
WHEN DATEDIFF(DAY,[tblDeals].[Actual Close Date], GETDATE())<91 THEN
'61-90'
Else
'91+'
End
End
End
End
End

FROM
tblDeals
LEFT JOIN tblNames
ON tblDeals.[Account Set Up Person Key] = tblNames.Number
LEFT JOIN tblNames AS tblNames_1
ON tblDeals.[Relationship Specialist Key] = tblNames_1.Number
LEFT JOIN tblNames AS tblNames_2
ON tblDeals.[Negotiation Specialist Key] = tblNames_2.Number
LEFT JOIN tblProductTypes
ON tblDeals.[Product Code] = tblProductTypes.Number
LEFT JOIN tblOffices
ON tblDeals.Office = tblOffices.Number
LEFT JOIN tblRegions
ON tblOffices.Region = tblRegions.Number

WHERE
CASE WHEN  [tblDeals].[Deal Status] <> 3 THEN
'N/A'
Else
CASE WHEN [tblDeals].[CDR Setup Done] IS NOT NULL THEN
'Complete'
Else
CASE WHEN [tblDeals].[CDR Setup Due] IS NULL AND [tblDeals].[CDR Setup
Done] IS NULL THEN
'Due Date Not Set'
Else
CASE WHEN [tblDeals].[CDR Setup Due] IS NOT NULL AND [tblDeals].[CDR Setup
Done] IS NULL THEN
CASE WHEN DATEDIFF(DAY,[tblDeals].[Actual Close Date], GETDATE())<61 THEN
'Open'
Else
'Past Due'
End
End
End
End
End IN ('Open', 'Past Due')


------------------------------------------------------------------------------
Electronic Privacy Notice. This e-mail, and any attachments, contains information that is, or may be, covered by electronic communications privacy laws, and is also confidential and proprietary in nature. If you are not the intended recipient, please be advised that you are legally prohibited from retaining, using, copying, distributing, or otherwise disclosing this information in any manner. Instead, please reply to the sender that you have received this communication in error, and then immediately delete it. Thank you in advance for your cooperation.
==============================================================================




More information about the dba-SQLServer mailing list