Change results

I have created query As shown:

SELECT distinct [Table] FROM [Database]

The results i get back are A, B & C. What i am trying to do is show a result as a different word.

i.e instead of displaying A is shows in drop down list as Apple, B shows Banana. etc.

Can anyone help please.

Thanks

Duncan:

It sounds to me like what you want to do is to join to a translation table to do a simple lookup; something like?

declare @.source table ( sourceKey varchar(8))
insert into @.source values ('A')
insert into @.source values ('B')
insert into @.source values ('C')

declare @.translate table ( sourceKey varchar(8), xlate varchar (10))
insert into @.translate values ('A', 'Apple')
insert into @.translate values ('B', 'Banana')
insert into @.translate values ('C', 'Cherry')

select xlate as Target
from @.source p
inner join @.translate q
on p.sourceKey = q.sourceKey

-- Target
-- -
-- Apple
-- Banana
-- Cherry

|||

The syntax of your query is not correct. Normally in SQL Server you would use the following:

SELECT [Columns] FROM [Table]

It is good practice to create a lookup table that contains a set of unique shorthand codes or IDs, such as 'A' and 'B' in your example, and an alphanumerical field to contain a description of the code/ID - 'Apple' or 'Banana' in your example. You would then join the fact table to the lookup table and return the appropriate columns from each table.

Could you post your table definitions and the query that you are using to return the data?

Chris

|||Thanks for that. I can see what you've done but the fields i need are carries in a field in a table. there can be only 3 results a b or c. there are alot of records. i don;t see how i can use this to select distinct from field in table but display as something else.|||I must agree with Chris; I don't know where I am going and I'm afraid that I need a roadmap.|||

Query

SELECT distinct [COLUMN] FROM [TABLE]

Returns

A
B
C

What i would like is for it to show

Apple
Banana
Cranberry

It is to populate a drop down list with SSRS. So that the users see what each entry means and not just ABC.

|||

In that case then the answers previously given to your original post will work, however if in the short term you want to make this really simple then you could use this:

SELECT 'Apple' AS [LookupValue], 'A' AS LookupCode UNION
SELECT 'Banana', 'B' UNION
SELECT 'Cranberry', 'C'
ORDER BY 1

The LookupValue is the value that you would display in your drop-down list, the LookupCode is the value that you feed back into the report's main query.

I wouldn't recommend the above approach as you'd need to modify the SELECT statement if you make changes to the reference data - you'd be far better off by using a lookup table as originally suggested, I've provided the example below to show how this can be done.

The choice is yours.

Chris

CREATE TABLE dbo.FruitType
(
FruitTypeCode CHAR(1) NOT NULL PRIMARY KEY,
FruitTypeValue VARCHAR(100) NOT NULL,
)
GO

--Ideally you would declare a Foreign Key constraint between the FruitTypeCode columns in your fact table and the following lookup table.
INSERT INTO dbo.FruitType(FruitTypeCode, FruitTypeValue)
SELECT 'A', 'Apple' UNION
SELECT 'B', 'Banana' UNION
SELECT 'C', 'Cranberry'
GO

--Use this to populate your combo - display FruitTypeValue and feed FruitTypeCode back into the report's main query.
SELECT FruitTypeCode, FruitTypeValue
FROM dbo.FruitType
ORDER BY FruitTypeValue
GO

|||

Ok got it working.

select distinct

case [COLUMN NAME]

when 'A' then 'Apple'

when 'B' then 'Banana'

else 'Unknown'end

from [TABLE NAME]

where [COLUMN NAME] in ('A', 'B', 'C')