June 21, 2013 at 7:21 am
Hello,
I have a query that returns several key values:
SELECT * FROM TableX
Which returns:
Value1 = 1001
Value2 = 9671
.....etc
Now Value1 & Value2 are stored in the same ValueTable. To get one translated to a textual representation I would join like so:
SELECT MRX.Col1 VAT.TextName, MRX.Col3, Col4....
FROM TableX MRX
INNER JOIN ValueTable VAT ON
MRX.Col2 = VAT.Value1
This is fine and dandy and gets one key translated to user friendly text but what about the other (Col4)?
Do I have to join again..add an OR clause or...?
SELECT MRX.Col1 VAT.TextName, MRX.Col3, VAT2.TextName....
FROM TableX MRX
INNER JOIN ValueTable VAT ON
MRX.Col2 = VAT.Value1
INNER JOIN ValueTable VAT2 ON
MRX.Col4 = VAT2.Value2
TIA'JB
June 21, 2013 at 9:03 am
To be sure you get a coded and tested solution, it would help if you could post up some sample data and an expected result set. The people who answer questions on these forums are unpaid. Setting up the problem for them so that they can just cut and paste is a simple courtesy which will pay you big dividends in timeliness and quality of the responses you get. There is a good read here[/url] that will explain what we need and why we need it. Thanks.
Edited to take a blind stab at answering.
Reading what you've posted for the third time, it believe you're saying that you have want to retrieve text values from different rows, and present them side by side as columns. Perhaps you have a descriptions table associated with codes in the original table. If that assumption is correct you only have to join once, but you need to either PIVOT or cross-tab to present the values as different columns.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 21, 2013 at 9:46 am
Here is an illustration of what I think you're talking about. If my assumptions are wrong, please change the code up to illustrate generally what your tables and data look like, and what you would expect the results to look like given the sample data you supply.
declare @descriptions table (code int identity primary key, code_description varchar (50))
insert into @descriptions
select 'small' union all
select 'medium' union all
select 'large' union all
select 'red' union all
select 'green' union all
select 'blue' union all
select 'stock' union all
select 'custom'
select * from @descriptions
declare @items table (itemNumber int , code int )
insert into @items
select 1,3 union all
select 1,6 union all
select 1,7
-- one join gets the descriptions
select itemnumber, i.code, d1.code_description, row_number() over(order by (select Null)) as rowID
from @items i
join @descriptions d1 on d1.code = i.code
where itemNumber = 1
-- this code cross-tabs the rows into columns
;with cte as (
select itemnumber, i.code, d1.code_description, row_number() over(order by (select Null)) as rowID
from @items i
join @descriptions d1 on d1.code = i.code
where itemNumber = 1
)
select itemnumber
, max(case when rowID = 1 then code_description else null end) as Col1
, max(case when rowID = 2 then code_description else null end) as Col2
, max(case when rowID = 3 then code_description else null end) as Col3
from cte
group by itemnumber
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply