Solved

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

Posted on 2013-01-18
12
176 Views
Last Modified: 2013-02-24
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 |
0
Comment
Question by:GD_GRAY
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 43

Assisted Solution

by:zephyr_hex (Megan)
zephyr_hex (Megan) earned 100 total points
ID: 38794998
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 100 total points
ID: 38795163
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
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 100 total points
ID: 38795348
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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

Author Comment

by:GD_GRAY
ID: 38801137
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 38801815
You have each column name twice and 2 truckIDs. How can you tell which piece of data belongs to which TruckID?
0
 

Author Comment

by:GD_GRAY
ID: 38802219
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
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 200 total points
ID: 38802251
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
 

Author Comment

by:GD_GRAY
ID: 38802273
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 38802570
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
 

Author Comment

by:GD_GRAY
ID: 38805693
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
 
LVL 35

Accepted Solution

by:
David Todd earned 200 total points
ID: 38806805
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
 

Author Closing Comment

by:GD_GRAY
ID: 38819003
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question