Help with query

[KirillToronto]

Hi I have a Table that defines progression of document. Here is the table1;
Name Dep1 Dep2 Dep3 Dep4
Route1 Acct Admin Sales Invoice
Route2 Acctg HR Sales Invoice
etc.

Then I have another table2
Route CurrDept other columns
Route1 Accounting etc

What I need to do is to find next and previous departments from from table2 on Table 1. Any clues ?

 

[markros]

You would need to fist UNPIVOT the table1.

PluralSight Learning Library

 

[KirillToronto]

I forgot to mention I am using SQL2000 thus no PIVOT UNPIVOT, however union all will do, I only have 18 columns

 

[RyanEK]

Can you provide your expected output?

 

[NastiaMurzin]

select Name, Dept1 from table1 where table1.dept1 = table2.currdept;
go
Basically, the value of currdept changes in this query. what the value of current department is
that's what the value is going to be looked for in the query search.

Also, if Dept1 is switched to Dept2, then the search of the query will be done on Dept2 column.

 

[SQLBill]

So, since this 'thing' is using Route1, you want to know where it 1) has been and 2) where it should be going to? And since Accounting is the first step, you just need to know where it is going to next?

Since table 1 has different entries for Accounting (it has Acct and Acctg) do you have a look up table that links abbreviations to the actual name in Table 2?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[NastiaMurzin]

SQL Bill, do not copy me!

 

[SQLBill]

NastiaMurzin,

Don't worry, I wasn't copying you. I read what you wrote and was kinda confused as to what you were asking, so I asked what I wanted to know. I also wanted the poster to clarify the issue with the different abbreviations for Accounting.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875