TSQL transform row values into one column table

Source Table

Col1  |Col2  |Col3  |Col4  | Col5
----------------------------------
 hi   | this | is   | a    | test

Destination Table

RowValues|
----------
hi
this
is
a
test

I am using Dynamic SQL. Any help ?

This is my code , just change table name and the Id in the where clause to what suits you

DECLARE @sql nVARCHAR(max), @TableName  nvarchar(100), @where nvarchar(max)
set @TableName = 'stockItems'

set @where= ' where id = 2'
select @sql  ='Select '
select  @sql = @sql+  + ' '''+ [name] +' = ''+ cast(' + [name]  + ' as nvarchar(10)) as '+[name]+',  ' 
         from sys.columns where object_name (object_id) = @TableName


set @sql = stuff(@sql, len(@sql), 1, '')  +  ' From '+@TableName+ @where
print @sql
set @sql = REPLACE(@sql,',   From',' From')
set @sql = @sql + '  '
print @sql
exec(@sql)

Now I need to create a new table that has one column that hold holds each value as a row

Thanks to @Mahmoud-Gamal The solution should be something like below

declare @cols nvarchar(max)
select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(column_name)
                      FROM information_schema.columns
                      WHERE table_name = 'vehicles' 
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');
declare @statement nvarchar(max)
set @statement ='
SELECT
    ColumnName,  Value
FROM
    Vehicles
UNPIVOT
    (
    Value
    FOR ColumnName
    IN
        (
        '+@cols+'
        )
    )
    AS A'

    execute(@statement)

Please change the "vehicle" table name to any table on your database that has columns from different types (datetime, int and nvarchar) and the below error is shown Any help ? The type of column "Description" conflicts with the type of other columns specified in the UNPIVOT list.

Solution

Use the UNPIVOT table operator:

SELECT col AS RowValues
FROM table1 AS t
UNPIVOT
(
  col
  FOR value IN([col1], 
               [col2], 
               [col3],
              [col4],
              [col5])
) AS u;

SQL Fiddle Demo

This will give you:

| ROWVALUES |
|-----------|
|        hi |
|      this |
|        is |
|         a |
|      test |

Update:

In case you don't know the names of the columns, and you want to do this dynamically, you have to do this using dynamic SQL.

But the problem is how to get the columns names?

You can get the columns names from the information_schema.columns, then concatenate them in one sql, then replace the columns' names in the UNPIVOT with this string, and execute that statement dynamically like this:

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(column_name)
                      FROM information_schema.columns
                      WHERE table_name = 'Table1'
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');

SELECT @query = ' SELECT col AS RowValues
                  FROM table1 AS t
                  UNPIVOT
                  (
                  val
                  FOR col IN ( ' + @cols + ' )
                  ) AS u;';

EXECUTE(@query);

Updated SQL Fiddle Demo