asked on

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

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.

Kindest regards,

PortletPaul

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 Shah

ASKER

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

PortletPaul

please answer the question "Is it OK if the columns headings remain the same every time you use this query?"

Ali Shah

ASKER

Hi Paul,

sorry missed it. Yes it is okay if the columns headings remain the same. like 'Ref, Ref1, Ref2' and so on

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ali Shah