Hello,
I need help in solving a task of my project. In my project there is a table where the student fill 6 preference of branch which he would like to join. The table look like this.
Appl_no pref1 pref2 pref3 pref4 pref5 pref6
-
125 5 4 1 6 8 2
126 2 3 4 1 7 6
127 5 2 1 6 4 3
128 2 3 5 7 1 4
The preference table looks like this:
Pref1 Branch
-
1 EEE
2 ECE
3 MECH
4 BT
5 IT
6 CIVIL
7 CHEM
8 ARCH
:
:
23 etc
I need the table which displays the preferences of students like this..
Appl_no EEE ECE MECH BT IT CIVIL CHEM ARCH
125 3 6 2 1 4 5
-
126 4 1 2 3 6 5
127 3 2 6 5 1 4
--
128 5 1 2 6 3 4
Please help me......
Regards
Ram
I try to create the query by unpivot,pivot and CTE, hope this help.
I assume that pref1 - pref6' value is unique for each Appl_no,
therefore, the value of Appl_no 128/pref6 change from 5 to 4.
Code Snippet
-- CREATE TABLE [t1565585a]
CREATE TABLE [dbo].[t1565585a]
(
[Appl_no] [int] NOT NULL,
[pref1] [int] NOT NULL,
[pref2] [int] NOT NULL,
[pref3] [int] NOT NULL,
[pref4] [int] NOT NULL,
[pref5] [int] NOT NULL,
[pref6] [int] NOT NULL,
CONSTRAINT [PK_t1565585a] PRIMARY KEY CLUSTERED
(
[Appl_no] ASC
)
) ON [PRIMARY]
insert into dbo.t1565585a values (125,5,4,1,6,8,2);
insert into dbo.t1565585a values (126,2,3,4,1,7,6);
insert into dbo.t1565585a values (127,5,2,1,6,4,3);
insert into dbo.t1565585a values (128,2,3,5,7,1,4);
-- CREATE TABLE [t1565585b]
CREATE TABLE [dbo].[t1565585b]
(
[Branch_no] [int] NOT NULL,
[Branch] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_t1565585b] PRIMARY KEY CLUSTERED
(
[Branch_no] ASC
)
) ON [PRIMARY]
insert into dbo.t1565585b values (1, 'EEE');
insert into dbo.t1565585b values (2, 'ECE');
insert into dbo.t1565585b values (3, 'MECH');
insert into dbo.t1565585b values (4, 'BT');
insert into dbo.t1565585b values (5, 'IT');
insert into dbo.t1565585b values (6, 'CIVIL');
insert into dbo.t1565585b values (7, 'CHEM');
insert into dbo.t1565585b values (8, 'ARCH');
-- execute Query
with
-- CTE: [ApplPrefBranch_no] : unpivot t1565585a
[ApplPrefBranch_no] ([Appl_no], [Pref_no], [Branch_no])
as
(
select
[Appl_no],
convert(int, substring([Pref_no], 5, 1)) as [Pref_no],
[Branch_no]
from
(
select
[Appl_no],
[pref1],
[pref2],
[pref3],
[pref4],
[pref5],
[pref6]
from dbo.t1565585a
) p
unpivot
(
[Branch_no]
for [Pref_no]
in
(
[pref1],
[pref2],
[pref3],
[pref4],
[pref5],
[pref6]
)
) pvt
),
-- CTE: [ApplPrefBranch] : replace Branch_no to Branch
[ApplPrefBranch] ([Appl_no], [Pref_no], [Branch])
as
(
select
[Appl_no],
[Pref_no],
[Branch]
from [ApplPrefBranch_no] [no]
left join [t1565585b] [b]
on [no].[Branch_no] = [b].[Branch_no]
)
-- generate result by pivot
select
[Appl_no],
[EEE],
[ECE],
[MECH],
[BT],
[IT],
[CIVIL],
[CHEM],
[ARCH]
from
(
select [Appl_no], [Branch], [Pref_no]
from [ApplPrefBranch]
) p
pivot
(
max([Pref_no])
for [Branch]
in
(
[EEE],
[ECE],
[MECH],
[BT],
[IT],
[CIVIL],
[CHEM],
[ARCH]
)
) pvt
If you use sql server 2005...
Code Snippet
Create Table #application (
[Appl_no] int ,
[pref1] int ,
[pref2] int ,
[pref3] int ,
[pref4] int ,
[pref5] int ,
[pref6] int
);
Insert Into #application Values('125','5','4','1','6','8','2');
Insert Into #application Values('126','2','3','4','1','7','6');
Insert Into #application Values('127','5','2','1','6','4','3');
Insert Into #application Values('128','2','3','5','7','1','5');
Create Table #branch (
[Id] int ,
[Branch] Varchar(100)
);
Insert Into #branch Values('1','EEE');
Insert Into #branch Values('2','ECE');
Insert Into #branch Values('3','MECH');
Insert Into #branch Values('4','BT');
Insert Into #branch Values('5','IT');
Insert Into #branch Values('6','CIVIL');
Insert Into #branch Values('7','CHEM');
Insert Into #branch Values('8','ARCH');
Select U.Appl_No,U.Pref,B.Branch BranchName into #Applications
From
(Select Appl_No,1 Pref,Pref1 Branch From #application
Union All
Select Appl_No,2,Pref2 From #application
Union All
Select Appl_No,3,Pref3 From #application
Union All
Select Appl_No,4,Pref4 From #application
Union All
Select Appl_No,5,Pref5 From #application
Union All
Select Appl_No,6,Pref6 From #application) as U Join #branch B On U.Branch=B.Id
Select Appl_No,[EEE],
[ECE],
[MECH],
[BT],
[IT],
[CIVIL],
[CHEM],
[ARCH] from (Select Appl_No, Pref, BranchName From #Applications) as PP
Pivot(
Min(Pref) For BranchName in
(
[EEE],
[ECE],
[MECH],
[BT],
[IT],
[CIVIL],
[CHEM],
[ARCH]
)
) as Pvt
|||If you use sql server 2000...
Create Table #application (
[Appl_no] int ,
[pref1] int ,
[pref2] int ,
[pref3] int ,
[pref4] int ,
[pref5] int ,
[pref6] int
);
Insert Into #application Values('125','5','4','1','6','8','2');
Insert Into #application Values('126','2','3','4','1','7','6');
Insert Into #application Values('127','5','2','1','6','4','3');
Insert Into #application Values('128','2','3','5','7','1','5');
Create Table #branch (
[Id] int ,
[Branch] Varchar(100)
);
Insert Into #branch Values('1','EEE');
Insert Into #branch Values('2','ECE');
Insert Into #branch Values('3','MECH');
Insert Into #branch Values('4','BT');
Insert Into #branch Values('5','IT');
Insert Into #branch Values('6','CIVIL');
Insert Into #branch Values('7','CHEM');
Insert Into #branch Values('8','ARCH');
Select U.Appl_No,U.Pref,B.Branch BranchName into #Applications
From
(Select Appl_No,1 Pref,Pref1 Branch From #application
Union All
Select Appl_No,2,Pref2 From #application
Union All
Select Appl_No,3,Pref3 From #application
Union All
Select Appl_No,4,Pref4 From #application
Union All
Select Appl_No,5,Pref5 From #application
Union All
Select Appl_No,6,Pref6 From #application) as U Join #branch B On U.Branch=B.Id
Select [Main].Appl_No
,[EEE].Pref [EEE]
,[ECE].Pref [ECE]
,[MECH].Pref [MECH]
,[BT].Pref [BT]
,[IT].Pref [IT]
,[CIVIL].Pref [CIVIL]
,[CHEM].Pref [CHEM]
,[ARCH].Pref [ARCH]
from
#application [Main]
Left Outer Join #Applications [EEE] On main.Appl_No = [EEE].Appl_No And [EEE].BranchName='EEE'
Left Outer Join #Applications [ECE] On main.Appl_No = [ECE].Appl_No And [ECE].BranchName='ECE'
Left Outer Join #Applications [MECH] On main.Appl_No = [MECH].Appl_No And [MECH].BranchName='MECH'
Left Outer Join #Applications [BT] On main.Appl_No = [BT].Appl_No And [BT].BranchName='BT'
Left Outer Join #Applications [IT] On main.Appl_No = [IT].Appl_No And [IT].BranchName='IT'
Left Outer Join #Applications [CIVIL] On main.Appl_No = [CIVIL].Appl_No And [CIVIL].BranchName='CIVIL'
Left Outer Join #Applications [CHEM] On main.Appl_No = [CHEM].Appl_No And [CHEM].BranchName='CHEM'
Left Outer Join #Applications [ARCH] On main.Appl_No = [ARCH].Appl_No And [ARCH].BranchName='ARCH'
|||
Hello Yoshihiro Kawabata ,
Thank you very much for helping me. I'm very much impressed.
Keep smiling,
No comments:
Post a Comment