Multiple rows to single row with multiple columns
Posted on 2011-03-15
I have a table that has information for users associated with the same company on MS SQL 2005
CompanyID | Name | Address | City | State | Zip | Phone
2 Name1 Address1 City1 State1 Zip1 phone1
2 Name2 Address2 City2 State2 Zip2 phone2
3 Name3 Address3 City3 State3 Zip3 phone3
I need to grab the users on the same company and return the data on a single row as follows
Name1 | Address1 | City1 | State1 | Zip1 | Name2 | Address2 | City2 | State2 | Zip2
Name1 Address1 City1 State1 Zip1 Name2 Address2 City2 State2 Zip2
Notice that the column names are numbered.
Is it possible to create a query to dynamically generate the column names with a number depending on the number of users associated to the company id (no more than 5 per company) and have a single row with all the results?
This is needed to populate a PDF that only accepts 1 row results from the query. the PDF has fields
Name1 Address1 City1 State1 Zip1 phone1
Name2 Address2 City2 State2 Zip2 phone2
Any help will be greatly appreciated.