Gubbala Ram


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





Re: Please help in solving a query..

Yoshihiro Kawabata


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







Re: Please help in solving a query..

Manivannan.D.Sekaran

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







Re: Please help in solving a query..

Manivannan.D.Sekaran

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'






Re: Please help in solving a query..

Gubbala Ram

Hello Yoshihiro Kawabata ,

Thank you very much for helping me. I'm very much impressed.

Keep smiling,