I have some data listed like the attached picture
SELECT Code, compCode FROM KitItems
The data is currently formatted like this:
But i want it to be transposed in to a number of columns
Like this:
Any ideas
-
Please read this: spaghettidba.com/2015/04/24/…MJH– MJH2018-10-10 11:01:50 +00:00Commented Oct 10, 2018 at 11:01
-
I have provided the script how it is now image How i want it after What have i done wrong?Fabio– Fabio2018-10-10 11:05:43 +00:00Commented Oct 10, 2018 at 11:05
Add a comment
|
1 Answer
You can use row_number() & do the conditional aggregation :
select code,
max(case when seq = 1 then compcode end) as one,
max(case when seq = 2 then compcode end) as two,
. . .
max(case when seq = 7 then compcode end) as seven
from (select t.*,
row_number() over (partition by code order by compcode) as seq
from table t
) t
group by code;
Same could be achieve via dynamic PIVOT SQL if code has too many compcodes.
