Making SQL query to give specific results like this

Question

I have a database table called dir which has rows like:

field1   field2    filed 3
1        name      sam 
1        age       18
1        class     12
2        name      jolly
2        age       14
2        class     8
.        .         .
.        .         .
.        .         .

I want to display it as follows using my SQL query

no      name     age    class
1       sam      18     12
2       jolly    14     8

Use pivot for this see the example https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Naveen, Aug 29 '19 at 13:23
That's why I consider EAV tables as anti-design. That's a pain to query — Cid, Aug 29 '19 at 13:25
For a dynamic solution, instead consider handling this in application code. — Strawberry, Aug 29 '19 at 14:25
One difficulty with a dynamic approach is getting the attributes (field2) to return in a meaningful order. — Strawberry, Aug 29 '19 at 14:39

score 1 · Answer 1 · answered Aug 29 '19 at 13:16

1

You can use conditional aggregation:

select field1,
       max(case when field2 = 'name' then field3 end) as name,
       max(case when field2 = 'age' then field3 end) as age,
       max(case when field2 = 'class' then field3 end) as class
from t
group by field1;

answered Aug 29 '19 at 13:16

Gordon Linoff

1,242,037
58
646
786

Could this be dynamic ? – Cid Aug 29 '19 at 13:26
@Cid . . . Yes, it could be. That would require dynamic SQL. – Gordon Linoff Aug 29 '19 at 16:06

score 1 · Answer 2 · edited Aug 29 '19 at 14:00

1

You can use conditional aggregation

select field1,max(case when field2='name' then field3 end) name,
max(case when field2='age' then field3 end) age,
max(case when field2='class' then field3 end) class
 from table group by field1

edited Aug 29 '19 at 14:00

Strawberry

33,750
13
40
57

answered Aug 29 '19 at 13:16

Zaynul Abadin Tuhin

31,407
5
33
63

Making SQL query to give specific results like this

2 Answers2