Row Column Comparision - 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

Transact-SQL (2000)

Row Column Comparision

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-08-03 : 02:47:27

Hi all,

I Have A Table Like This

Create Table Ai_Company_Level
(
Organisation_Code Varchar(10),
Element_Code Varchar(10),
From_Process_Period Int,
To_Process_Period int,
BS Varchar(3),
HRA Varchar(3),
MPA Varchar(3),
LTA Varchar(3),
MM Varchar(3),
CON Varchar(3),
ARINC Varchar(3),
AR_ER Varchar(3),
AR_DE Varchar(3),
SA Varchar(3),
MISCD Varchar(3),
CD Varchar(3),
PTAX Varchar(3),
PF Varchar(3),
ESIEE Varchar(3)
)
Insert into Ai_Company_Level
Select 'RSWMLNJA' ,'PTAX' ,'1'
,'2' ,'YES' ,'YES'
,'YES' ,'NO' ,'YES'
,'YES' ,'YES' ,'YES'
,NULL ,NULL ,NULL
,NULL ,NULL ,NULL
,NULL

Create Table Ai_Elemnts
(
Organisation_Code Varchar(10),
Element_Code Varchar(10),
From_Process_Period Int,
To_Process_Period int,
)
insert into Ai_Elemnts
Select 'RSWMLNJA' ,'BS' ,'1' ,'2'
Union all Select 'RSWMLNJA' ,'HRA' ,'1' ,'2'
Union all Select 'RSWMLNJA' ,'MPA' ,'1' ,'2'
Union all Select 'RSWMLNJA' ,'LTA' ,'1' ,'2'
Union all Select 'RSWMLNJA' ,'MM' ,'1' ,'2'
Union all Select 'RSWMLNJA' ,'CON' ,'1' ,'2'
Union all Select 'RSWMLNJA' ,'ARINC' ,'1' ,'2'
Union all Select 'RSWMLNJA' ,'AR_ER' ,'1' ,'2'
Union all Select 'RSWMLNJA' ,'AR_DE' ,'1' ,'2'
Union all Select 'RSWMLNJA' ,'SA' ,'1' ,'2'
Union all Select 'RSWMLNJA' ,'MISCD' ,'1' ,'2'
Union all Select 'RSWMLNJA' ,'CD' ,'1' ,'2'
Union all Select 'RSWMLNJA' ,'PTAX' ,'1' ,'2'
Union all Select 'RSWMLNJA' ,'PF' ,'1' ,'2'
Union all Select 'RSWMLNJA' ,'ESIEE' ,'1' ,'2'

What i want is

i want to check the Element_Code(Ai_Elemnts table) column in Ai_Company_Level columns thats is bs ,HRA.. etc
if that column value is yes means i need to take the element

i.e Ai_Elemnts.row value = Ai_Company_Level.column_name

How can i do this
please help me
Pulling my hair out

Thanks
KK

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-03 : 02:54:50

is the Ai_Company_Level.Element_Code related to Ai_Elements.Element_Code ?

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-08-03 : 03:06:49

yes ,

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-08-03 : 03:12:00

The Ai_Company_level Table Have The Values Like

Organisation_Code Element_Code From_Process_Period To_Process_Period BS HRA MPA LTA MM CON ARINC AR_ER AR_DE SA MISCD CD PTAX PF ESIEE
RSWMLNJA PTAX 1 0 YES YES YES NO YES YES YES YES
RSWMLNJA MM 1 0 YES YES YES YES YES YES YES YES

(2 row(s) affected)

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-08-03 : 03:13:18

Jen Expliain me More
Please
--KK

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-08-03 : 03:15:34

And The Ai_Elemnts Have the values like

Organisation_Code Element_Code From_Process_Period To_Process_Period
----------------- ------------ ------------------- -----------------
RSWMLNJA BS 1 2
RSWMLNJA HRA 1 2
RSWMLNJA MPA 1 2
RSWMLNJA LTA 1 2
RSWMLNJA MM 1 2
RSWMLNJA CON 1 2
RSWMLNJA ARINC 1 2
RSWMLNJA AR_ER 1 2
RSWMLNJA AR_DE 1 2
RSWMLNJA SA 1 2
RSWMLNJA MISCD 1 2
RSWMLNJA CD 1 2
RSWMLNJA PTAX 1 2
RSWMLNJA PF 1 2
RSWMLNJA ESIEE 1 2

(15 row(s) affected)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-03 : 03:24:12

quote:
i want to check the Element_Code(Ai_Elemnts table) column in Ai_Company_Level columns thats is bs ,HRA.. etc
if that column value is yes means i need to take the element

so you need to do this ?

Ai_Company_Level INNER JOIN Ai_Elements
ON Ai_Company_Level.Element_Code = Ai_Elements.Element_Code

i am a bit confuse with your statement here.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-08-03 : 03:36:25

ah...I see what you mean...

but what exactly do you need to do? take out in what sense?

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-03 : 03:47:35

Table Ai_Company_Level does not look normalize. And hence you have the problem joining.

Here is a view that transpose the table

create view Ai_Co_lvl
as 
select	Organisation_Code, Element_Code, From_Process_Period, To_Process_Period, 'BS' as E_Code, BS as E_Value
from	Ai_Company_Level
union all
select	Organisation_Code, Element_Code, From_Process_Period, To_Process_Period, 'HRA' as E_Code, HRA as E_Value
from	Ai_Company_Level
union all
select	Organisation_Code, Element_Code, From_Process_Period, To_Process_Period, 'MPA' as E_Code, MPA as E_Value
from	Ai_Company_Level

Then you can JOINing it to the Ai_Elements table. Is this what you need to do ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources