How to use pivot in sql into multiple columns

Question

Can somebody help me with SQL using pivot?

For example I have a list shown below with 1 column:

and I want the result to become this:

I tried pivot but I only got 1 row.. I just need multiple rows.

why would you want such output , just out of curiosity what's the real life use case ? — eshirvana
– eshirvana, Commented May 28, 2021 at 1:56

eshirvana · Accepted Answer · 2021-05-28 01:59:58Z

2

You can use window functions. In your case, you have a sequence with no gaps, so you can just use modulo arithmetic:

    select max(case when units % 10 = 1 then units end),
           max(case when units % 10 = 2 then units end),
           . . .
           max(case when units % 10 = 0 then units end)
    from t
    group by ceiling(units / 10.0);

edited May 28, 2021 at 1:59

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

answered May 28, 2021 at 1:56

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

How to use pivot in sql into multiple columns

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related