CODEWITHSUNDEEP
mysqlsql
Den
Den

Reputation: 379

How to select Column value as Column name with conditions in SQL table

I'm new to SQL. I have a table 'Customers' and it looks like this.

enter image description here

I would like to select 'Gender' AS Gender Column and 'Age' as Age column which would be like this.

enter image description here

I've tried several ways but it still doesn't show what I need. Please help me.

Upvotes: 4

Views: 41624

Answers (3)

grantmcconnaughey
grantmcconnaughey

Reputation: 10689

I have not tested this, but give something like this a try:

Select c.Name,
    (Select c2.Value from customers c2 Where c2.Name = c.Name And c2.Field = 'Gender') as Gender,
    (Select c2.Value from customers c2 Where c2.Name = c.Name And c2.Field = 'Age') as Age
From Customers c
Group By c.Name

PS I apologize for the awful formatting...had to type this from my phone.

Upvotes: 5

peterm
peterm

Reputation: 92785

One way to go about it is to use conditional aggregation

SELECT name,
       MAX(CASE WHEN field = 'Gender' THEN value END) gender,
       MAX(CASE WHEN field = 'Age' THEN value END) age
  FROM customers
 GROUP BY name

The other way (if you're interested only in these two columns) would be 

SELECT c1.name, c1.value gender, c2.value age
  FROM customers c1 JOIN customers c2
    ON c1.name = c2.name
   AND c1.field = 'Gender'
   AND c2.field = 'Age';

Assumption is that both Gender and Age exist for each Name. It it's not the case then use an OUTER JOIN instead of an INNER JOIN like so

SELECT n.name, c1.value gender, c2.value age
  FROM
(
  SELECT DISTINCT name
    FROM customers
) n LEFT JOIN customers c1
    ON n.name = c1.name AND c1.field = 'Gender' 
    LEFT JOIN customers c2
    ON n.name = c2.name AND c2.field = 'Age';

Output:

|   NAME | GENDER | AGE |
|--------|--------|-----|
| Angela | Female |  28 |
|  Davis |   Male |  30 |

Here is SQLFiddle demo

Upvotes: 13

Victor Zhang
Victor Zhang

Reputation: 211

You can use SHOW COLUMNS FROM Customers to get the name of the columns

Then just use SELECT * FROM Customers to retrieve the data

Upvotes: -4

Related Questions