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 |
|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 |
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You have each column name twice and 2 truckIDs. How can you tell which piece of data belongs to which TruckID?
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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/
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/
ASKER
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 ?
This cant be done can it ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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.