Skip to content
Advertisement

Using PIVOT to get SELECT into one column, not row

I am trying to make a select, which would show the result into ONE column, not row. I tried using PIVOT, but I am not successful at all.

Result of my select:

Customer_ID:   InsertionDate:  Name:   Birth:                                                
  1234          2020-01-01     John  1993-01-01

I want to have it like this:

                 MyColumn                                               
 Customer_ID:    1234   
 InsertionDate:  2020-01-01  
 Name:           John  
 Birth:          1993-01-01

Advertisement

Answer

You can use cross apply to obtain a single column:

declare @test table (Customer_ID int,  InsertionDate date,  Name varchar(50), Birth date)   
insert into @test values (1234, '2020-01-01','John', '1993-01-01')

select  val as MyColumn
from @test
    CROSS APPLY 
    (
    VALUES
        ('Customer_ID:'+   cast(Customer_ID as varchar)),
        ('InsertionDate:'+ cast(InsertionDate as varchar)),
        ('Name:'+ cast(Name as varchar)),
        ('Birth:'+ cast(Birth as varchar))
    )CS (val)

results:

enter image description here

or, if you prefer having a separate column for descriptions:

select 
    Col as ColumnName, 
    val as MyColumn
from @test
    CROSS APPLY 
    (
    VALUES
        ('Customer_ID:'  , cast(Customer_ID as varchar)),
        ('InsertionDate:', cast(InsertionDate as varchar)),
        ('Name:'         , cast(Name as varchar)),
        ('Birth:'        , cast(Birth as varchar))
    )CS (col, val)

output:

enter image description here

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement