How to choose PIVOT for this table?

Question

I have a table like this:

Id          Code                 Value
----------- -------------------- -----------
1           A                    100
2           B                    100
3           C                    220
4           A                    150
5           C                    300
6           D                    120
7           E                    120

And this is my expectation:

Code1       Value1      Code2       Value2      Code3       Value3
----------- ----------- ----------- ----------- ----------- -----------
A           100         B           100         C           220
A           150         C           300         D           120
E           120

Should I use PIVOT for this case (and how to do it) or a regular query of choice?

Answer 1

This is also not with the rotation axis, but in case you need to group elements into groups of 3 ordered by identifier, and the identifier has spaces, for example, sometimes this happens, this should do it:

select
  ROW,
  max(case when COL = 0 then Code end) as Code1,
  max(case when COL = 0 then Value end) as Value1,
  max(case when COL = 1 then Code end) as Code2,
  max(case when COL = 1 then Value end) as Value2,
  max(case when COL = 2 then Code end) as Code3,
  max(case when COL = 2 then Value end) as Value3 
FROM (
  select
    (row_number() over (order by ID)-1) % 3 as COL,
    (row_number() over (order by ID)-1) / 3 as ROW,
    Code,
    Value
  from
    data
) X
group by ROW

This calculates the number of rows and columns based on row_number that increment by identifier, and then uses the group to break the results into groups of 3.

SQL Fiddle

Answer 2

Perhaps PIVOTnot useful here.

SELECT  t1.code as code1, t1.value as value1,
        t2.code as code2, t2.value as value2, 
        t3.code as code3, t3.value as value3 
FROM tab as t1
    left join tab as t2
        ON t1.id = t2.id - 1
    left join  tab as t3
        ON t2.id = t3.id - 1
where ((t1.id-1) % 3) = 0