How to convert unknown no of rows to columns in SQL Server

Hi EE,

I am struggling to convert rows to columns in SQL Server. I know there's pivot function but my situation is different which i try to explain below:

This is my original record set
Original Records
There can be one value against a booking id field or there can be 100s of values against a booking id. I want to convert first 9 values into rows against each booking id. So if there's one value it creates one column and if there are 9 it create 9 columns.

below is an example of what i need.
Required records
Kindest regards,
Ali ShahSQL DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
Can you provide sample data as text instead of images, a spreadsheet with both the data and an example of the wanted result would help.

Is it OK if the columns headings remain the same every time you use this query?
Ali ShahSQL DeveloperAuthor Commented:
Hi PorletPaul,

Thanks a lot for your message. i have attached the sheet containing the data and the example of the requirement. Should you need any other information please do let me know.

regards,

Ali
Data.xlsx
PortletPaulEE Topic AdvisorCommented:
please answer the question "Is it OK if the columns headings remain the same every time you use this query?"
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Ali ShahSQL DeveloperAuthor Commented:
Hi Paul,

sorry missed it. Yes it is okay if the columns headings remain the same. like 'Ref, Ref1, Ref2' and so on
PortletPaulEE Topic AdvisorCommented:
This assume that you want a fixed number of items per bookingid, and that the headings are static.

sSELECT
      BookingId
    , MAX(CASE WHEN rn = 1 THEN value END) AS ref1
    , MAX(CASE WHEN rn = 2 THEN value END) AS ref2
    , MAX(CASE WHEN rn = 3 THEN value END) AS ref3
    , MAX(CASE WHEN rn = 4 THEN value END) AS ref4
    , MAX(CASE WHEN rn = 5 THEN value END) AS ref5
    , MAX(CASE WHEN rn = 6 THEN value END) AS ref6
    , MAX(CASE WHEN rn = 7 THEN value END) AS ref7
    , MAX(CASE WHEN rn = 8 THEN value END) AS ref8
    , MAX(CASE WHEN rn = 9 THEN value END) AS ref9
FROM (
      SELECT
            BookingId
          , value
          , ROW_NUMBER() OVER (PARTITION BY BookingId ORDER BY ( SELECT 1)  ) AS rn
      FROM yourtable
      ) d
GROUP BY
      BookingId
ORDER BY
      BookingId
;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ali ShahSQL DeveloperAuthor Commented:
Oh wow. This is brilliant. Wish i could also learn something like this. You are a super star
PortletPaulEE Topic AdvisorCommented:
Thanks. If you do it many times you get to remember how :)  Cheers.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.