Home » bring the output into single column from a table as vertical order [closed]

bring the output into single column from a table as vertical order [closed]

Solutons:


There is a similar question here.
You can achieve this using CROSS APPLY with UNION ALL:

select 
 c.x
from [mytable]
cross apply
(
 select [Name] union all
 select CAST([Age] as varchar) union all --all the columns must be of the same type
 select [Type] union all
 select [City] 
) c (x)
where x is not null ;

As pointed out by @Andriy M it could be also written like this:

select x
from [mytable]
cross apply
(VALUES ([Name]), (CAST([Age] as varchar)), ([Type]), ([City]))  c(x) -- parentheses are required
WHERE x IS NOT NULL

For more detail see here.

The above query does not guarrantee any particular order of records in the resultset – to get a particular order of records in a resultset it is allways necessary to use the ORDER BY clause.

The issue here is mostly order. Otherwise you could just do SELECT Name FROM Table UNION ALL SELECT Age...

So let’s assign some values to order by:

--This order is arbitrary, and will order the rows in whatever order they come.
WITH NumberedRows AS
(SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rownum
 FROM Table
)
--Now do a union all with things to column numbers too. Alias the first set, and cast Age to a string
, Unioned AS
(SELECT rownum, Name as Value, 1 as ColOrder
 FROM NumberedRows
 UNION ALL
 SELECT rownum, CAST(Age AS VARCHAR(3)), 2 as ColOrder
 FROM NumberedRows
 UNION ALL
 SELECT rownum, [Type], 3 as ColOrder
 FROM NumberedRows
 UNION ALL
 SELECT rownum, City, 4 as ColOrder
 FROM NumberedRows
)
--Now strip the nulls and extra columns, and order
SELECT Value
FROM Unioned
WHERE Value IS NOT NULL
ORDER BY rownum, ColOrder;

Related Solutions

Why is it so expensive to develop an MMO? [closed]

The first problem is that the software itself is very complicated, particularly for a new or inexperienced game developer. You have to maintain (at the very least) a client and server application while providing more content than you would expect for a...

Did I just get hacked?

EDIT 2: there is one good reason why this post is attracting so much attention: you managed to record the whole, live session of an intruder on your PC. This is very different from our everyday experience, where we deal with the discovery of the consequences of...

How to delete a non-empty directory in Terminal?

Use the below command : rm -rf lampp It deletes all files and folders contained in the lampp directory. In case user doesn't have the permission to delete the folder: Add sudo at the beginning of the command : sudo rm -rf folderName Otherwise, without sudo you...

mysql: Show GRANTs for all users

Nothing built-in. You have two options though: Use common_schema's sql_show_grants view. For example, you can query: SELECT sql_grants FROM common_schema.sql_show_grants; Or you can query for particular users, for example: SELECT sql_grants FROM...

Configurable values to MDB annotations

You can externalise the annotations into the ejb-jar.xml that you deploy in the META-INF of your jar file as follows: <?xml version="1.0" encoding="UTF-8"?> <ejb-jar version="3.0"> <enterprise-beans> <message-driven>...

How do I select which Apache MPM to use?

There are a number of MPM modules (Multi-Processing Modules), but by far the most widely used (at least on *nix platforms) are the three main ones: prefork, worker, and event. Essentially, they represent the evolution of the Apache web server, and the different...

Using var self = this or .bind(this)? [closed]

Things that would favor var self = this; bind isn't supported in IE8 and Safari5. If you aim to build a library or code that supports legacy browsers, then var self = this would be more cross-browser friendly. Sometimes, callbacks are bound to a certain context...

What is the difference between SSL vs SSH? Which is more secure?

SSL and SSH both provide the cryptographic elements to build a tunnel for confidential data transport with checked integrity. For that part, they use similar techniques, and may suffer from the same kind of attacks, so they should provide similar security (i.e....

How can I stop applications and services from running?

First Things First You may have some misconceptions about how Android works and what's really happening when a service is running or an app is in the background. See also: Do I really need to install a task manager? Most apps (e.g., ones you launch manually)...

How do I reset a lost administrative password?

By default the first user's account is an administrative account, so if the UI is prompting you for a password it's probably that person's user password. If the user doesn't remember their password you need to reset it. To do this you need to boot into recovery...

How can I use environment variables in Nginx.conf

From the official Nginx docker file: Using environment variables in nginx configuration: Out-of-the-box, Nginx doesn't support using environment variables inside most configuration blocks. But envsubst may be used as a workaround if you need to generate your...

Difference between .bashrc and .bash_profile

Traditionally, when you log into a Unix system, the system would start one program for you. That program is a shell, i.e., a program designed to start other programs. It's a command line shell: you start another program by typing its name. The default shell, a...

Custom query with Castle ActiveRecord

In this case what you want is HqlBasedQuery. Your query will be a projection, so what you'll get back will be an ArrayList of tuples containing the results (the content of each element of the ArrayList will depend on the query, but for more than one value will...

What is the “You have new mail” message in Linux/UNIX?

Where is this mail? It's likely to be in the spool file: /var/mail/$USER or /var/spool/mail/$USER are the most common locations on Linux and BSD. (Other locations are possible – check if $MAIL is set – but by default, the system only informs you about...

How can I find the implementations of Linux kernel system calls?

System calls aren't handled like regular function calls. It takes special code to make the transition from user space to kernel space, basically a bit of inline assembly code injected into your program at the call site. The kernel side code that "catches" the...