How would I reformat a query if all the data in returned is in one column and I need it in 5 columns ?

If all the data I need can be found in one column how can I get sql to change it from rows into columns. Lets say the column has row 1, row 2, row 3, row 4 ..etc

|username    |
|city              |
|state             |
|zip                |
|username     |
|city                |
|state             |
|zip                 |

Can I get the query to change it to 4 columns per-row

|username | city | state | zip |
|username | city | state | zip |
GD_GRAYAsked:
Who is Participating?
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

Part of your issue is that you have a collision on the column names. That is, its difficult to have two columns called TruckID etc. You need to come up with the logic of how to differentiate them or combine them in an aggregate function.

For starters try the transpose function in Excel. Maybe you can program Excel to do this for you, as its not easy or easily achieved in just sql.

HTH
  David
0
 
zephyr_hex (Megan)Connect With a Mentor DeveloperCommented:
0
 
Patrick MatthewsConnect With a Mentor Commented:
Is that the only column in your table?  If not, can you please post a few rows of sample data, and the output you would expect given the sample?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
ZberteocConnect With a Mentor Commented:
What kind of table is that? How can you have different information in the same column? I am not saying is not possible but you need to give some example. Even if you have a column let's sau called Info and you have:

Info
---------
somenick
New Yprk
NY
1234-1234

How do you know which is which? You would have to have a second column that says what info that is:

Name | Info
username | some guy
city | new York
state | NY
zip | 1234-1234
0
 
GD_GRAYAuthor Commented:
Sorry, the thing is that in order to get the data I need I have to use the inner join the column names are in one table and all the data is in another. SQL Statement= "SELECT EXTRA_INFO_COLS.COL_NAME, EXTRA_INFO_DATA.COL_DATA
FROM EXTRA_INFO_DATA
      INNER JOIN EXTRA_INFO_COLS ON EXTRA_INFO_DATA.COL_ID = EXTRA_INFO_COLS.COL_ID
      WHERE EXTRA_INFO_DATA.EXTRA_ID ='10' ORDER BY EXTRA_INFO_DATA.COL_ID"

The result pulls data like the attached image. What I need to do is find a way to add the unpivot into this statement so the column names are listed as columns and the field data falls inline under each column.  Sql result
0
 
ZberteocCommented:
You have each column name twice and 2 truckIDs. How can you tell which piece of data belongs to which TruckID?
0
 
GD_GRAYAuthor Commented:
The data follows suit in the order it is placed in the table. So the ID in the first TruckID in row 1 would also use the first Available data from row 3.
0
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

Is this a one-off or a regular report?

If a one-off, use the transpose paste-special option in Excel.

HTH
  David
0
 
GD_GRAYAuthor Commented:
No.... sadly its going to work out to be  daily, and once I  ( WE, if I can be so bold ) get it to work I'll have to setup a Sp and make a job for it.
0
 
ZberteocCommented:
You could use the PIVOT function but if the column names change it won't be very practical. The use of PIVOT works when you know for sure what the column content will be every time you run the query and you use the data as column names. In you case you could have a different result if you change the where clause condition.

So you can turn something like years in columns because you know they are the same but what do you do if the column name changes from Redispatch to Milage. You would have to come up with a PIVOT query for any combination that you would come up in the application.

Example using PIVOT:

http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/
0
 
GD_GRAYAuthor Commented:
Wow I'm not having any luck understanding UnPivot and how to apply it to this. All the samples I find use the column names as is, none of the samples show anything about how to pivot the Data in column 1 into a single row with each cell accounting for a new column and than do the same for column 2 or put the data from column 2 in the columns made from the data in column 1.

This cant be done can it ?
0
 
GD_GRAYAuthor Commented:
Thank you all, they are all good suggestions but so far I'm not able to apply them to what I need. I'm going to abandon the cause. I took the long road and built a new table, one with a proper structure...lol  Thank you for your time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.