Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Returning Columns from a table vertically

Posted on 2008-06-17
2
Medium Priority
?
236 Views
Last Modified: 2013-11-05
Hi,
  I have a table which I want to return its data with a query in a vertical way in a single column, for example I have this table

  table_name
  | Field_1 |  Field 2 | Field 3  |
  --------------------------------
  | Value1 | Value2  | Value3 |

 And I need to get
   
  Field_1 = Value1
  Field_2 = Value2
  Field_3 = Value3
 

Thxs in advance
0
Comment
Question by:negreteo
2 Comments
 
LVL 22

Expert Comment

by:dportas
ID: 21806652
How will you make sense of the result if there is more than one row? Is the order significant?

SELECT col, val
FROM
 (SELECT 'col1', col1
  FROM tbl
  UNION ALL
  SELECT 'col2', col2
  FROM tbl
  UNION ALL
  SELECT 'col3', col3
  FROM tbl) T(col,val)
/* ORDER BY ??? something unspecified ? */ ;

0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 21809937
hi, similar to @dportas solution above, but, if I read that properly....

you need to do a union from selecting all the different columns one at a time... have taken the value content as being numeric, not exceeding 20 characters - you might need to adjust accordingly...

select new_column
from (
select 'Field_1 = '+convert(varchar(20),isnull(value1,0)) as new_column from mytable
union all
select 'Field_2 = '+convert(varchar(20),isnull(value2,0)) from mytable
union all
select 'Field_3 = '+convert(varchar(20),isnull(value3,0)) from mytable
--etc
) c
order by new_column
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Microsoft Jet database engine errors can crop up out of nowhere to disrupt the working of the Exchange server. Decoding why a particular error occurs goes a long way in determining the right solution for it.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

577 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