CODEWITHSUNDEEP
sql
user3804871
user3804871

Reputation: 1

Access Column values to Header

I have a table, "Sort_age" in a file called "Info".

And what i currently have in "Sort_age" is as below:

year    month   code    Category    Field   Info
2013    jan      3      student     Name    John
2013    jan      3      student     Age      14
2013    feb      2      teacher     Name    Mika
2013    feb      2      teacher     Age      25

However, i would like my output to be as such:

year    month   code    Category    Name    Age
2013    jan      3      student     John    14
2013    feb      2      teacher     Mika    25

May I know how do i go about doing it in microsoft ACCESS, SQL query?

Thank you!!

Upvotes: 0

Views: 50

Answers (3)

SHEKHAR SHETE
SHEKHAR SHETE

Reputation: 6066

Try this: Demo here

MS SQL Server 2008 Schema Setup:

Create table Sort_age
(year nvarchar(50),month nvarchar(50),code int,Category  nvarchar(50),Field  nvarchar(50),Info nvarchar(50))

insert into Sort_age
(year ,month ,code,Category,Field,Info)
Values

('2013','jan',3,'student','Name','John'),
('2013','jan',3,'student','Age','14'),
('2013','feb',2,'teacher','Name','Mika'),
('2013','feb',2,'teacher','Age','25')

Query 1:

SELECT YEAR,MONTH,CODE,CATEGORY,NAME=MAX(INFO) , AGE =MIN(INFO)
FROM Sort_age 
GROUP BY YEAR,MONTH,CODE,CATEGORY 
ORDER BY CODE DESC

Results:

| YEAR | MONTH | CODE | CATEGORY | NAME | AGE |
|------|-------|------|----------|------|-----|
| 2013 |   jan |    3 |  student | John |  14 |
| 2013 |   feb |    2 |  teacher | Mika |  25 |

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try this

SELECT year,Month,Code,Category,
       MAX(CASE WHEN Field = 'Name' THEN Info ELSE null END) AS Name,
       Max(CASE WHEN Field = 'Age' THEN Info ELSE null END) AS Age
FROM Sort_age
GROUP BY year,Month,Code,Category

Fiddle Demo

Output would be

| YEAR | MONTH | CODE | CATEGORY | NAME | AGE |
|------|-------|------|----------|------|-----|
| 2013 |   jan |    3 |  student | John |  14 |
| 2013 |   feb |    2 |  teacher | Mika |  25 |

Upvotes: 0

Azar
Azar

Reputation: 1867

If it is sql server use this

SELECT YEAR,MONTH,CODE,CATEGORY,NAME=MAX(INFO) , AGE =MIN(INFO)
FROM Table 
GROUP BY YEAR,MONTH,CODE,CATEGORY 
ORDER BY CODE DESC

Upvotes: 0

Related Questions