cross tab query.

Question

Post reply

cross tab query.

tppradeep18

SSC Journeyman

Points: 80
More actions
March 28, 2004 at 4:05 am

#101939

hi all.
i have a table called codeMaster which has the Following structuer and some data
CodeMaster Table
-------------
Code Desc
----- ------
001 England
002 USA
003 Russia
004 department1
005 Departmen2
. . . . .
I have another table called details which has the following fields that
contain the code from the above table
Details Table
-----------
Name Country Department
------ --------- -------------
John 002 004
Bill 001 005
. . . . . . . . . . .. etc.
I want to write a query which will return me a query like below
Name Country Department
------ --------- --------------
John USA Department1
do I write the query to get the above result. The Details table will contain more than 20
fields. Also Codemaster table will have more than 1000 codes. I dont know how to write such
a query in the most efficient manner. any help would be greately appreciated. thanks!

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Your will have to use multiple joins. For you example it will be

SELECT

Det.[Name],

CM1.Desc AS Country,

CM2.Desc AS Department

FROM

dbo.Details Det

Inner Join

dbo.Codemaster CM1

ON

Det.Country = CM1.Code

Inner Join

dbo.Codemaster CM2

ON

Det.Department = CM2.Code

tppradeep18 SSC Journeyman Points: 80 More actions · Answer 2

HI,

I could have done with inner joins,but my main concern is if I have say 50 fields, would I require to have 50 inner joins for each field in details table!!!. if so how efficient is it.. As u have shown u have already INNER JOINed Details with two Codemaster references CM1, CM2 because I have given only two fields in Details table.. what if the Details table had 100 fields... this is my core concern.. thanks.

rgds

pradep

Antares686 SSC Guru Points: 125444 More actions · Answer 3

You might could try with 1 join and do and OR to get each columns match (but this will create multiple rows). From there on each column you use a CASE if match with number then Desc else NULL and rollup with a group by.

Something like

SELECT

Det.[Name],

MAX(CASE WHEN Det.Country = CM.Code THEN CM.Desc ELSE NULL END) AS Country,

MAX(CASE WHEN Det.Department = CM.Code THEN CM.Desc ELSE NULL END) AS Department

FROM

dbo.Details Det

Inner Join

dbo.Codemaster CM

ON

Det.Country = CM.Code OR

Det.Department = CM.Code

GROUP BY

Det.[Name]

Of course a large number of rows may cause a slow down and a large number of columns could too. And you could incorrect results if Det.[Name] is not unique.

I might be inclined to just use the joins instead or reconsider the design.

gljjr SSChampion Points: 10080 More actions · Answer 4

Could you add a codetype table so that you can join to it to make the cross tab easier? If you logically group the codes in CodeMaster into a codetype then add the code type id field to the codemaster table you should be able to group the data easier and get your cross table working easier. Then each field name in your cross tab report would be a record value from your codetype table (Country, Department, etc).

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

tppradeep18 SSC Journeyman Points: 80 More actions · Answer 5

Hi gary,

thanks a lot for that suggestion. I have done what u said. created a codetypemaster table. here is what I have comeout with

select * from codetypemaster CTM join codemaster CDM on ctm.nmCodeGroup = CDM.nmCodeGroup

where CDM.nmCode in (2,4)

2 and 4 are codes from codemaster where 2 is one of the codes from a group of codes for country and 4 is one of the codes from a group of department..

any more tips on optimization on the above or any other way that i can do the same.

thanks a lot.