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
170 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
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 42

Assisted Solution

by:zephyr_hex
zephyr_hex 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 26

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
 

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 26

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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 26

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now