How can I transform a table with 1 row with varchar columns into a column?

Question

Hi, In SQL server How can I transform 1 row with varchar columns into a column? I think I need to use Pivot but I can't find an example without agregators

this is the situation I have:

create table #tmp ( ac varchar(100), bc varchar(100), cc varchar(100))
insert into #tmp (ac,bc,cc)
Values ('test1','test2','test3')

insert into #tmp
Values ('test4','test5','test6')

SELECT *
FROM #tmp
WHERE ac='test1'

drop table #tmp

I need to transform the result of the select in a column, I will use the result column in another nested query.

Thank you!

Answer 1

You need to use UNPIVOT, not PIVOT

create table #tmp ( ac varchar(100), bc varchar(100), cc varchar(100))

insert into #tmp (ac,bc,cc)

Values ('test1','test2','test3')

SELECT
 *
FROM
 #tmp

SELECT
 *
FROM
 #tmp
UNPIVOT
(
 [Column] FOR Data IN (ac, bc, cc)
) uPIVOT


drop table #tmp