How to convert a single row table into columns?

Question

I have a single row query returning data in this format:

Col1    Col2    Col3    Col4
-----------------------------
1425    3454    2345    3243

I want it to display it in this format:

Col1  |  1425
Col2  |  3454
Col3  |  2345
Col4  |  3243

How can I do it?

I am avoiding to use UNION method since the above table is extracted from a query and for each <table_name> I would have to paste the table query which will make the process slow.

In this case, seeing the original query might make it easier for someone to make a suggestion. — Tim Biegeleisen, Nov 22 '21 at 07:13
You might be looking for SQL Server's [UNPIVOT](https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15#unpivot-example) but you need a key column as well. — AlwaysLearning, Nov 22 '21 at 07:14
Does this answer your question? [SQL Server : Columns to Rows](https://stackoverflow.com/questions/18026236/sql-server-columns-to-rows) — Pred, Nov 22 '21 at 07:15
you can do union all for each column if you dont want to unpivot . like select 'col1' , Col1 from tbl union all select 'col2' , Col2 from tbl — Ali Fidanli, Nov 22 '21 at 07:29

score 1 · Answer 1 · answered Nov 26 '21 at 09:54

If the number of fields per table is always constant, then it might work like this.

DECLARE @Table TABLE(
    [Col1] int,
    [Col2] int,
    [Col3] int,
    [Col4] int
)

INSERT INTO @Table VALUES(1425, 3454, 2345, 3243); -- some Test data

SELECT * FROM @Table; -- row

SELECT 
    p.[Columns],
    p.[Value]
FROM (
    SELECT 
        [Col1],
        [Col2],
        [Col3],
        [Col4]
    FROM @Table
) x
UNPIVOT(
    [Value] FOR [Columns] IN ([Col1],[Col2],[Col3],[Col4]) -- 
) AS P;

score 0 · Answer 2 · answered Nov 22 '21 at 07:57

You can cross join your query with the column names in order to show the column values in separate rows:

select
  columns.col,
  case columns.col
    when 'Col1' then q.col1
    when 'Col2' then q.col2
    when 'Col3' then q.col3
    when 'Col4' then q.col4
  end as value
from ( <your query here> ) q
cross join ( values ('Col1'), ('Col2'), ('Col3'), ('Col4') ) as columns(col);

How to convert a single row table into columns?

2 Answers2