Select vertical table to horizontal table SQL

Question

I have the following table:

------------------------------------------- ID Group myDate Value ------------------------------------------- 1 A 2014-06-01 100 2 A 2014-06-02 200 3 A 2014-06-03 300 4 B 2014-06-01 50 5 B 2014-06-02 100 6 B 2014-06-03 125

I want to have the following result:

------------------------------------------- myDate Value myDate Value ------------------------------------------- 2014-06-01 100 2014-06-01 50 2014-06-02 200 2014-06-02 100 2014-06-03 300 2014-06-03 125

So I want to make it horizontal view base on Group field. Does anyone know How to do this?

Thank you.

how many rows on the table might have the same date? always just 2, or could there be 3, 4, 5 etc. ? — Brian DeMilia
– Brian DeMilia, Commented Sep 8, 2014 at 2:45
also are the group values predictable (A, B, C, etc.) or is there much variation? — Brian DeMilia
– Brian DeMilia, Commented Sep 8, 2014 at 2:46
@BrianDeMilia, Actually only A, B, and C.. But let say it could be more that that. — Haminteu
– Haminteu, Commented Sep 8, 2014 at 3:00

Brian DeMilia · Accepted Answer · 2014-09-08 03:04:46Z

1

Fiddle: http://sqlfiddle.com/#!6/df9dc/1/0

select min(case when grp = 'A' then mydate end) as mydate1, min(case when grp = 'A' then value end) as value1, min(case when grp = 'B' then mydate end) as mydate2, min(case when grp = 'B' then value end) as value2 from tbl group by mydate

Will be cumbersome if you have a lot of groups but if there are just a couple more it shouldn't be too hard to add the necessary case statements for the others.

answered Sep 8, 2014 at 3:04

Brian DeMilia

13.3k1 gold badge25 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Haminteu Over a year ago

Cheers mate,, it works! But need to find out, How if the Groups is more than that.

Yogesh86 Over a year ago

But this is not the best approach as if u have the list grows it would make your code cumbersome so its better to go for pivot :)

Brian DeMilia Over a year ago

even with pivot you would still have to add to the code for additional groups, it is not dynamic. @Haminteu to add case statements for additional groups just copy and paste the ones already there but change A or B to C or whatever the next group is

Collectives™ on Stack Overflow

Select vertical table to horizontal table SQL

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related