CODEWITHSUNDEEP
mysqlsql
learning
learning

Reputation: 73

how to get the table column names as entries in result column set?

The database scheme consists:

Table1(code, col1, col2, col3, col4, col5)

  1. What to do is: For the Table1 with the maximal code value from Table1 table, obtain all its characteristics (except for a code) in two columns:
    • The name of the characteristic (a name of a corresponding column in the PC table);
    • Value of the characteristic.

I don't have any idea how to get the table column names in my result column set. The final result will look like:

chr value
col1    133
col2    80
col3    28
col4    2
col5    50

Upvotes: 1

Views: 1250

Answers (4)

mkorcek
mkorcek

Reputation: 1

Select char, value
From 
(Select code,
cast(speed as varchar(20)) speed,
cast(ram as varchar(20)) ram,
cast(hd as varchar(20)) hd,
cast(model as varchar(20)) model,
cast(cd as varchar(20)) cd,
cast(price as varchar(20)) price
FROM pc
) src
UNPIVOT 
(value For char in (speed,ram,hd,model,cd,price)
) As unpvt
where 
code in (Select max(code) from PC)`

Upvotes: 0

Gala
Gala

Reputation: 1

SELECT 'cd' as chr, cd as value 
FROM pc 
WHERE code = (SELECT max(code) FROM pc)

UNION

SELECT  'model' as chr, cast(model as varchar)as value 
FROM pc 
WHERE code = (SELECT max(code) FROM pc)

UNION

SELECT  'speed' as chr,cast(speed as varchar)  as value 
FROM pc 
WHERE code = (SELECT max(code) FROM pc)

UNION

SELECT  'ram' as chr, cast(ram as varchar) as value 
FROM pc 
WHERE code = (SELECT max(code) FROM pc)

UNION

SELECT  'hd' as chr, cast(hd as varchar)  as value 
FROM pc 
WHERE code = (SELECT max(code) FROM pc)

UNION

SELECT  'price' as chr,cast(price as varchar)  as value 
FROM pc 
WHERE code = (SELECT max(code) FROM pc)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

This is an unpivot operation. The simplest way is using union all. However, the following is generally more efficient:

select (case when n.n = 1 then 'col1'
             when n.n = 2 then 'col2'
             when n.n = 3 then 'col3'
             when n.n = 4 then 'col4'
             when n.n = 5 then 'col5'
        end) as chr,
       (case when n.n = 1 then col1
             when n.n = 2 then col2
             when n.n = 3 then col3
             when n.n = 4 then col4
             when n.n = 5 then col5
        end) as value
from table t cross join
     (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5
     ) n;

This is more efficient when your table is big or a complicated subquery.

The union all version is:

select 'col1', col1 from table t union all
select 'col2', col2 from table t union all
select 'col3', col3 from table t union all
select 'col4', col4 from table t union all
select 'col5', col5 from table t;

Upvotes: 1

Ulrich Thomas Gabor
Ulrich Thomas Gabor

Reputation: 6654

SELECT 'col1', MAX(col1) FROM table1
UNION
SELECT 'col2', MAX(col2) FROM table1
UNION
...

Upvotes: 0

Related Questions