How to convert a single row table into columns?-CodePudding

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.

CodePudding user response：

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);

CodePudding user response：

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;