Inserting Select statement within join

Question

Post reply

Inserting Select statement within join

pwalter83

SSChampion

Points: 14575
More actions
January 27, 2011 at 2:10 am

#144448

Hello,
I am trying to create a query wherein I need to create multiple columns using the same field from a table and based on a condition. I am wits end on how to achieve this now.
For e.g.- I need to create the following columns using the same field, EQ_CNT from the table MG_BOOKING_EQUIPMENT_REQ based on a condition(using KL_EQUIPMENT_TYPE_CD) :
Column Name --------- Source
20DRY-----------SELECT EQ_CNT WHERE KL_EQUIPMENT_TYPE_CD ='20D86'
40DRY-----------SELECT EQ_CNT WHERE KL_EQUIPMENT_TYPE_CD ='40D86'
40DHC-----------SELECT EQ_CNT WHERE KL_EQUIPMENT_TYPE_CD ='40D96'
45DRY-----------SELECT EQ_CNT WHERE KL_EQUIPMENT_TYPE_CD ='45D96'
20REF-----------SELECT EQ_CNT WHERE KL_EQUIPMENT_TYPE_CD ='20R86'
40REF----------- SELECT EQ_CNT WHERE KL_EQUIPMENT_TYPE_CD ='40R86'
HCREF-----------SELECT EQ_CNT WHERE KL_EQUIPMENT_TYPE_CD ='40R96'
This is in addition to adding other columns using other different tables. Should I use the select statement within the join to achieve this ? Could some one please shed some light on it....Thanks in advance for your help.
Kind Regards,
Paul

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

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Try this:

SELECT

[20D86] AS [20DRY] ,

[40D86] AS [40DRY] ,

[40D96] AS [40DHC] ,

[45D96] AS [45DRY] ,

[20R86] AS [20REF] ,

[40R86] AS [40REF] ,

[40R96] AS [HCREF]

FROM

( SELECT EQ_CNT , KL_EQUIPMENT_TYPE_CD FROM Table ) PIVOT_TABLE

PIVOT

(

MAX(EQ_CNT)

FOR KL_EQUIPMENT_TYPE_CD IN

(

[20D86] ,[40D86],[40D96] ,[45D96],

[20R86] ,[40R86],[40R96]

)

) PIVOT_HANDLE

{Edit : Added FROM to the query}

pwalter83 SSChampion Points: 14575 More actions · Answer 2

Hi ColdCoffee,

Thanks a lot for your reply. However, when I ran your query I only get the max value(only 1) for each of the column. There are more than a million values for each of the condition.

I tried to remove the max part from the query but it returned an error....Do you have any ideas ?

Thanks,

Paul

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

This ?

SELECT

CASE WHEN KL_EQUIPMENT_TYPE_CD = '20D86' THEN EQ_CNT END AS [20DRY] ,

CASE WHEN KL_EQUIPMENT_TYPE_CD = '40D86' THEN EQ_CNT END AS [40DRY] ,

CASE WHEN KL_EQUIPMENT_TYPE_CD = '40D96' THEN EQ_CNT END AS [40DHC] ,

CASE WHEN KL_EQUIPMENT_TYPE_CD = '45D96' THEN EQ_CNT END AS [45DRY] ,

CASE WHEN KL_EQUIPMENT_TYPE_CD = '20R86' THEN EQ_CNT END AS [20REF] ,

CASE WHEN KL_EQUIPMENT_TYPE_CD = '40R86' THEN EQ_CNT END AS [40REF] ,

CASE WHEN KL_EQUIPMENT_TYPE_CD = '40R96' THEN EQ_CNT END AS [HCREF]

FROM Table

pwalter83 SSChampion Points: 14575 More actions · Answer 4

Thanks a lot, ColdCoffee, that worked like a charm !!

Kind Regards,

Paul

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

ColdCoffee

SSC-Dedicated

Points: 39972

January 27, 2011 at 8:32 am

#1279117

You're Welcome, Walter!

pwalter83 SSChampion Points: 14575 More actions · Answer 6

Hi ColdCoffee,

I am sorry this was my mistake...your query works fine even in the scenario when more than 10 case statements exist....I confused this with the nested case statements...sorry to bother you...

Thanks again !!

Kind Regards,

Paul