Convert rows in columns with informix query

Question

I want to convert

inpvacart       inpvapvta inpvapvt1 inpvapvt2 inpvapvt3 inpvapvt4
CS-279          270.4149    0.0000    0.0000    0.0000   0.0000
AAA5030         1.9300      1.9300    1.6212    0.0000   0.0000

Query

select  
    inpvacart, 
    inpvapvta, 
    inpvapvt1, 
    inpvapvt2, 
    inpvapvt3, 
    inpvapvt4
from inpva;

into this

  inpvacart   line  value
  CS-279       1    270.4149
  CS-279       2    0.00000
  CS-279       3    0.00000
  CS-279       4    0.00000
  CS-279       5    0.00000
  AAA5030      1    1.9300
  AAA5030      2    1.9300
  AAA5030      3    1.6212    
  AAA5030      4    0.0000
  AAA5030      5    0.0000

I have tried this

 select s.inpvacart,l.lista,l.resultados
 from inpva as s,
table(values(1,s.inpvapvta),
            (2,s.inpvapvt1),
            (3,s.inpvapvt2),
            (4,s.inpvapvt3),
            (5,s.inpvapvt4))
)as l(lista,resultados);

But it does not work in informix 9

Is there a way to transpose rows to columns?

Thank You

What are you doing with Informix 9.x? It should have been put out to pasture a long time ago. Versions 10 and 11.10 are also obsolete; version 11.50 is out of marketing (so you can't buy it unless you already have it). 11.70 is the 'old' version and 12.10 is the current version. — Jonathan Leffler
– Jonathan Leffler, Commented Sep 3, 2015 at 21:07
@JonathanLeffler I don't know Informix so my answer was generic sql, but can you tell me if there is a better way to do this in Informix? Maybe there is some unpivot/transpose thing? — jpw
– jpw, Commented Sep 3, 2015 at 21:14
@jpw: Your answer is spot on. It saved me having to type the same thing, so thank you. — Jonathan Leffler
– Jonathan Leffler, Commented Sep 3, 2015 at 21:15

jpw · Accepted Answer · 2015-09-03 20:52:16Z

3

I don't think Informix has any unpivot operator to transpose columns to rows like for instance MSSQL does, but one way to do this is to transpose the columns manually and then use union to create a single set like this:

select inpvacart, 1 as line, inpvapvta as value from inpva
union all
select inpvacart, 2 as line, inpvapvt1 as value from inpva
union all
select inpvacart, 3 as line, inpvapvt2 as value from inpva
union all
select inpvacart, 4 as line, inpvapvt3 as value from inpva
union all
select inpvacart, 5 as line, inpvapvt4 as value from inpva
order by inpvacart, line;

It's not very pretty but it should work.

answered Sep 3, 2015 at 20:52

jpw

45k6 gold badges71 silver badges90 bronze badges

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

Collectives™ on Stack Overflow

Convert rows in columns with informix query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related