I have a table with columns:
Name, english, french, spanish, german
values (eg)
John, 20, 20, 30, 30
I want to diplay the data as:
john, 20 john, 20 john, 30 john, 30
how do i do this?
Thanx in advance..You can concatenate character compatible column values using the + operator.
In this case you will have to convert the numeric columns to VARCHAR or CHAR
do the concatenation like:
SELECT Name,
CAST( English AS VARCHAR(3) ) + SPACE( 1 ) + Name,
CAST( french AS VARCHAR(3) ) + SPACE( 1 ) + Name,
...
FROM tbl ;
As a side note, depending on your business model, it might be better to
represent language identifiers in a single column. It is more logical,
allows easier enforcement of constraints, offers better flexibility in
general querying and allows you to add more language identifiers in the
table without altering the schema.
--
Anith|||Select Name, english from table
union all
Select Name, french from table
union all
..
..
..
Madhivanan|||hey thanx :)
just what i need..|||rj wrote:
>> Hi all,
>>
>> I have a table with columns:
>> Name, english, french, spanish, german
>> values (eg)
>> John, 20, 20, 30, 30
>> I want to diplay the data as:
>> john, 20 john, 20 john, 30 john, 30
>> how do i do this?
>>
>> Thanx in advance..
>Madhivanan wrote:
> Select Name, english from table
> union all
> Select Name, french from table
> union all
Assuming english, french, spanish, and german are all in the same table,
as you stated above, it seems a simple select query can accomplish
what you want without the overhead of UNION (not sure if that's an issue):
SELECT Name, english, Name, french, Name, spanish, Name, german
FROM table
will display:
john, 20, john, 20, john 30, john, 30
