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

Ali Shah
Ali Shah used Ask the Experts™
on
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,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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 Developer

Author

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 Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
please answer the question "Is it OK if the columns headings remain the same every time you use this query?"
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Ali ShahSQL Developer

Author

Commented:
Hi Paul,

sorry missed it. Yes it is okay if the columns headings remain the same. like 'Ref, Ref1, Ref2' and so on
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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

Ali ShahSQL Developer

Author

Commented:
Oh wow. This is brilliant. Wish i could also learn something like this. You are a super star
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Thanks. If you do it many times you get to remember how :)  Cheers.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial