Please help in solving a query..

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,

please help

I have SQL 2000. I have one table tmpJcrewUpdates and another table
Jcrewupdates.
I have a task that reads a text file and imports all of it into
tmpjcrewupdates. The floowing script I am try to get it to either update
exsistign information in Jcrewupdates if the REF# is already in teh
jcrewupdates table. If not, I want the entire data for that ref# to be
sent to the jcrewupdates table.
For some reason, it isn't updating exsisting records, it just adds new ones
if the ref# isn't already there.
update [JCrewUpdates]
set [do rec'd] = [tmpJCrewUpdates].[do rec'd],[bill of ladin
g #] =
[tmpJCrewUpdates].[Bill of lading #],[container] =
[tmpJCrewUpdates].[container],Vessel=[tmpJCrewUpdates].Vessel,CF
LNY=[tmpJCrewUpdates].CFLNY,ETA=[tmpJCrewUpdates].ETA,LFD=[tmpJC
rewUpdates].LFD,terminal=[tmpJCrewUpdates].terminal,cfs=[tmpJCrewUpd
ates].cfs,Ctns=[tmpJCrewUpdates].Ctns,status=[tmpJCrewUpdat
es].status,[p/u
date]=[tmpJCrewUpdates].[p/u date],[del. date]=[tmpJCrewUpda
tes].[del. date]
From [JCrewUpdates]
Join [tmpJCrewUpdates]
on [tmpJCrewUpdates].[ref#]=[JCrewUpdates].[ref#]
Insert into [JCrewUpdates]([do rec'd],ref#,vessel,[BILL OF LADIN
G
#],cflny,eta,LFD,container,terminal,cfs,
ctns,status,[p/u date],[del.
date])
Select [do rec'd],[ref#],vessel,[bill of lading
#],cflny,eta,lfd,container,terminal,cfs,
ctns,status,[p/u date],[del.
date]
From [tmpJCrewUpdates]
Where NOT Exists (Select * from [JCrewUpdates] Where
[JCrewUpdates].[ref#]=[tmpJCrewUpdates].[ref#])Hi John
These are your queries re-written slighly,
UPDATE j
SET [do rec'd] = t.[do rec'd],
[bill of lading #] = t.[Bill of lading #],
[container] = t.[container],
Vessel=t.Vessel,
CFLNY=t.CFLNY,
ETA=t.ETA,
LFD=t.LFD,
terminal=t.terminal,
cfs=t.cfs,
Ctns=t.Ctns,
status=t.status,
[p/u date]=t.[p/u date],
[del. date]=t.[del. date]
FROM [JCrewUpdates] J
JOIN [tmpJCrewUpdates] t ON t.[ref#]=j.[ref#]
INSERT INTO [JCrewUpdates]([do rec'd],[ref#],vessel,
[bill of lading #],cflny,eta,LFD,container,terminal,
cfs,ctns,status,[p/u date],[del. date])
SELECT t.[do rec'd],t.[ref#],t.vessel,
t.[bill of lading #],t.cflny,t.eta,t.lfd,t.container,t.terminal,
t.cfs,t.ctns,t.status,t.[p/u date],t.[del. date]
FROM [tmpJCrewUpdates] t
LEFT JOIN [tmpJCrewUpdates] t ON t.[ref#]=j.[ref#]
WHERE t.[ref#] IS NULL
I could not see anything that would cause the updates not to happen, if the
following returns any rows then you should get updates;
SELECT t.[do rec'd],t.[ref#],t.vessel,
t.[bill of lading #],t.cflny,t.eta,t.lfd,t.container,t.terminal,
t.cfs,t.ctns,t.status,t.[p/u date],t.[del. date]
FROM [tmpJCrewUpdates] t
JOIN [tmpJCrewUpdates] t ON t.[ref#]=j.[ref#]
If not, you may have some problems with the values in the [ref#] columns
John
"Johnfli" wrote:

> I have SQL 2000. I have one table tmpJcrewUpdates and another table
> Jcrewupdates.
> I have a task that reads a text file and imports all of it into
> tmpjcrewupdates. The floowing script I am try to get it to either update
> exsistign information in Jcrewupdates if the REF# is already in teh
> jcrewupdates table. If not, I want the entire data for that ref# to be
> sent to the jcrewupdates table.
> For some reason, it isn't updating exsisting records, it just adds new one
s
> if the ref# isn't already there.
>
>
> update [JCrewUpdates]
> set [do rec'd] = [tmpJCrewUpdates].[do rec'd],[bill of lad
ing #] =
> [tmpJCrewUpdates].[Bill of lading #],[container] =
> [tmpJCrewUpdates].[container],Vessel=[tmpJCrewUpdates].Vessel,CFLNY=&#
91;tmpJCrewUpdates].CFLNY,ETA=[tmpJCrewUpdates].ETA,LFD=[tmpJCrewUpdates].LF
D,terminal=[tmpJCrewUpdates].terminal,cfs=[tmpJCrewUpdates].cfs,Ctns=[tm
pJCrewUpdates].Ctns,status=[tmpJCrewUpd
ates].status,[p/u
> date]=[tmpJCrewUpdates].[p/u date],[del. date]=[tmpJCrewUp
dates].[del. date]
> From [JCrewUpdates]
> Join [tmpJCrewUpdates]
> on [tmpJCrewUpdates].[ref#]=[JCrewUpdates].[ref#]
> Insert into [JCrewUpdates]([do rec'd],ref#,vessel,[BILL OF LAD
ING
> #],cflny,eta,LFD,container,terminal,cfs,
ctns,status,[p/u date],[de
l. date])
> Select [do rec'd],[ref#],vessel,[bill of lading
> #],cflny,eta,lfd,container,terminal,cfs,
ctns,status,[p/u date],[de
l. date]
> From [tmpJCrewUpdates]
> Where NOT Exists (Select * from [JCrewUpdates] Where
> [JCrewUpdates].[ref#]=[tmpJCrewUpdates].[ref#])
>
>

please help

I have SQL 2000. I have one table tmpJcrewUpdates and another table
Jcrewupdates.
I have a task that reads a text file and imports all of it into
tmpjcrewupdates. The floowing script I am try to get it to either update
exsistign information in Jcrewupdates if the REF# is already in teh
jcrewupdates table. If not, I want the entire data for that ref# to be
sent to the jcrewupdates table.
For some reason, it isn't updating exsisting records, it just adds new ones
if the ref# isn't already there.
update [JCrewUpdates]
set [do rec'd] = [tmpJCrewUpdates].[do rec'd],[bill of lading #] = [tmpJCrewUpdates].[Bill of lading #],[container] = [tmpJCrewUpdates].[container],Vessel=[tmpJCrewUpdates].Vessel,CFLNY=[tmpJCrewUpdates].CFLNY,ETA=[tmpJCrewUpdates].ETA,LFD=[tmpJCrewUpdates].LFD,terminal=[tmpJCrewUpdates].terminal,cfs=[tmpJCrewUpdates].cfs,Ctns=[tmpJCrewUpdates].Ctns,status=[tmpJCrewUpdates].status,[p/u
date]=[tmpJCrewUpdates].[p/u date],[del. date]=[tmpJCrewUpdates].[del. date]
From [JCrewUpdates]
Join [tmpJCrewUpdates]
on [tmpJCrewUpdates].[ref#]=[JCrewUpdates].[ref#]
Insert into [JCrewUpdates]([do rec'd],ref#,vessel,[BILL OF LADING
#],cflny,eta,LFD,container,terminal,cfs,ctns,status,[p/u date],[del. date])
Select [do rec'd],[ref#],vessel,[bill of lading
#],cflny,eta,lfd,container,terminal,cfs,ctns,status,[p/u date],[del. date]
From [tmpJCrewUpdates]
Where NOT Exists (Select * from [JCrewUpdates] Where
[JCrewUpdates].[ref#]=[tmpJCrewUpdates].[ref#])Hi John
These are your queries re-written slighly,
UPDATE j
SET [do rec'd] = t.[do rec'd],
[bill of lading #] = t.[Bill of lading #],
[container] = t.[container],
Vessel=t.Vessel,
CFLNY=t.CFLNY,
ETA=t.ETA,
LFD=t.LFD,
terminal=t.terminal,
cfs=t.cfs,
Ctns=t.Ctns,
status=t.status,
[p/u date]=t.[p/u date],
[del. date]=t.[del. date]
FROM [JCrewUpdates] J
JOIN [tmpJCrewUpdates] t ON t.[ref#]=j.[ref#]
INSERT INTO [JCrewUpdates]([do rec'd],[ref#],vessel,
[bill of lading #],cflny,eta,LFD,container,terminal,
cfs,ctns,status,[p/u date],[del. date])
SELECT t.[do rec'd],t.[ref#],t.vessel,
t.[bill of lading #],t.cflny,t.eta,t.lfd,t.container,t.terminal,
t.cfs,t.ctns,t.status,t.[p/u date],t.[del. date]
FROM [tmpJCrewUpdates] t
LEFT JOIN [tmpJCrewUpdates] t ON t.[ref#]=j.[ref#]
WHERE t.[ref#] IS NULL
I could not see anything that would cause the updates not to happen, if the
following returns any rows then you should get updates;
SELECT t.[do rec'd],t.[ref#],t.vessel,
t.[bill of lading #],t.cflny,t.eta,t.lfd,t.container,t.terminal,
t.cfs,t.ctns,t.status,t.[p/u date],t.[del. date]
FROM [tmpJCrewUpdates] t
JOIN [tmpJCrewUpdates] t ON t.[ref#]=j.[ref#]
If not, you may have some problems with the values in the [ref#] columns
John
"Johnfli" wrote:
> I have SQL 2000. I have one table tmpJcrewUpdates and another table
> Jcrewupdates.
> I have a task that reads a text file and imports all of it into
> tmpjcrewupdates. The floowing script I am try to get it to either update
> exsistign information in Jcrewupdates if the REF# is already in teh
> jcrewupdates table. If not, I want the entire data for that ref# to be
> sent to the jcrewupdates table.
> For some reason, it isn't updating exsisting records, it just adds new ones
> if the ref# isn't already there.
>
>
> update [JCrewUpdates]
> set [do rec'd] = [tmpJCrewUpdates].[do rec'd],[bill of lading #] => [tmpJCrewUpdates].[Bill of lading #],[container] => [tmpJCrewUpdates].[container],Vessel=[tmpJCrewUpdates].Vessel,CFLNY=[tmpJCrewUpdates].CFLNY,ETA=[tmpJCrewUpdates].ETA,LFD=[tmpJCrewUpdates].LFD,terminal=[tmpJCrewUpdates].terminal,cfs=[tmpJCrewUpdates].cfs,Ctns=[tmpJCrewUpdates].Ctns,status=[tmpJCrewUpdates].status,[p/u
> date]=[tmpJCrewUpdates].[p/u date],[del. date]=[tmpJCrewUpdates].[del. date]
> From [JCrewUpdates]
> Join [tmpJCrewUpdates]
> on [tmpJCrewUpdates].[ref#]=[JCrewUpdates].[ref#]
> Insert into [JCrewUpdates]([do rec'd],ref#,vessel,[BILL OF LADING
> #],cflny,eta,LFD,container,terminal,cfs,ctns,status,[p/u date],[del. date])
> Select [do rec'd],[ref#],vessel,[bill of lading
> #],cflny,eta,lfd,container,terminal,cfs,ctns,status,[p/u date],[del. date]
> From [tmpJCrewUpdates]
> Where NOT Exists (Select * from [JCrewUpdates] Where
> [JCrewUpdates].[ref#]=[tmpJCrewUpdates].[ref#])
>
>

pl/sql wrappers newbie guide?

Does anyone know of a good web reasource for writing wrappers for a low level c program?
I've been given the task to write a simple wrapper by tomorrow so I don't have time to get to the book store for an O'Reilly book.
Many thanks!
CraigTry: asktom.oracle.com

Search for "external procedure" "language c"|||Thanks Tony!

Pivot Task Error - Duplicate pivot key

I am using the pivot task to to a pivot of YTD-Values and after that I use derived columns to calculate month values and do a unpivot then.

All worked fine, but now I get this error message:

[ytd_pivot [123]] Error: Duplicate pivot key value "6".

The settings in the advanced editor seem to be correct (no duplicate pivot key value) and I am extracting the data from the source sorted by month.

Could it be a problem that I use all pivot columns (month 1 to 12) in the derived colum transformation and they aren′t available at this moment while data extracting is still going on?

any hints?

Cheers
Markus

The pivot transform takes values like:

cust# Product Qty

-- -

1 Ham 4

1 Chips 2

1 Flan 1

2 Chips 3

2 Beer 19

and produces rows like:

cust# HamQty ChipsQty FlanQty BeerQty

-- - - - -

1 4 2 1 null

2 null 3 null 19

so what to do with input data like this?

cust# Product Qty

-- -

1 Ham 4

1 Chips 2

1 Chips 5

Which value should go into the ChipsQty column 2 or 5?

Most application would want 7, and so we suggest that the pivot be preceded by an aggregate transform to ensure that there is only 1 row for each distinct value of the pivot key. If not, you will see the error you report.

hope this helps