[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

reorder table from 4 column to 1 column

Posted on 2005-05-04
6
Medium Priority
?
236 Views
Last Modified: 2010-03-19
Hi,

Kindly assist how can we requery 4 columns to be place to a single column.

table called scores
columns:
singing dancing eating voice
  10        9           8       10


output to a single column:
10
9
9
10

Thanks.
0
Comment
Question by:EdwardPeter
6 Comments
 
LVL 14

Accepted Solution

by:
Renante Entera earned 800 total points
ID: 13924859
Hi EdwardPeter!

You can simply do it using UNION ALL.  

Here's the query :

Select singing as ColumnName
From TableName
UNION ALL
Select dancing as ColumnName
From TableName
UNION ALL
Select eating as ColumnName
From TableName
UNION ALL
Select voice as ColumnName
From TableName

Hope this helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0
 
LVL 7

Assisted Solution

by:Jonathan Kelly
Jonathan Kelly earned 400 total points
ID: 13924872
this might be one way

Select singing from tbl
UNION
Select dancing from tbl
UNION
Select eating from tbl
UNION
Select voice from tbl



0
 
LVL 11

Assisted Solution

by:lluthien
lluthien earned 800 total points
ID: 13924880
although you might want to consider adding an extra column to be able to identify where the scores come from,
like so:

Select 'singing' as sourcecolumn, singing as ColumnName
From TableName
UNION ALL
Select 'dancing' as sourcecolumn, dancing as ColumnName
From TableName
UNION ALL
Select eating' as sourcecolumn, eating as ColumnName
From TableName
UNION ALL
Select 'voice' as sourcecolumn, voice as ColumnName
From TableName


so your result is:
sourcecolumn columname
singing          10
dancing           9
eating             9
voice             10

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:EdwardPeter
ID: 13924884
entrance2002,

How can we give the column a name?

Thanks.
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 13924960
Hi EdwardPeter!

Thanks for accepting my comment as the answer.  I'm glad that I have helped you.

Hope to help you again...


Regards!
eNTRANCE2002 :-)
0
 
LVL 11

Expert Comment

by:lluthien
ID: 13925265
saw that one coming
EdwardPeter :)
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

867 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