-1
select ID, Vehicle from table

Original result is 

ID      Vehicle
--------------------
1       Car
1       Bike
1       Scooter
...
1       N.Product

Result I am expecting

ID    Vehicle1   Vehicle2   Vehicle3  ....VehicleN
-----------------------------------------------------
1     Car         Bike       Scooter       N.Product

I went through examples related to pivot, but they didn't help. Any suggestions?

Mr_gemini
  • 43
  • 1
  • 6
  • It's a pivot, for sure. What did you try that "didn't help"? – Caius Jard Nov 08 '19 at 20:16
  • 2
    If it "didn't help" because you have N columns and most pivot operations are for a known number of columns, see https://stackoverflow.com/questions/52761674/sql-server-dynamic-pivot-for-an-unknow-number-of-columns - but consider also that converting a variable number of rows into a variable number of columns is usually a bad idea. If it's for reporting purposes, have your front end do it, not the db – Caius Jard Nov 08 '19 at 20:18
  • due to limitations at code side, i had to handle it in sql-@CaiusJard – Mr_gemini Nov 08 '19 at 20:47
  • i tried many examples, spent almost 2 days on this-@CaiusJard – Mr_gemini Nov 08 '19 at 20:48

2 Answers2

1

You can use row_number() and pivot or conditional aggregation:

select id,
       max(case when seqnum = 1 then vehicle end) as vehicle_1,
       max(case when seqnum = 2 then vehicle end) as vehicle_2,
       . . .
       max(case when seqnum = n then vehicle end) as vehicle_n
from (select t.*, 
             row_number() over (partition by id order by vehicle) as seqnum
      from t
     ) t
group by id;

This assumes that you know the number of columns that you want in the result set. If that is not the case, you would need to use dynamic SQL -- or aggregate the vehicles into a string.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

i did it by creating a temp table then using while loop, add columns and update values

Mr_gemini
  • 43
  • 1
  • 6