title: TSQL transform row values into one column table
body: <h2>Source Table</h2>
  
  <pre><code>Col1  |Col2  |Col3  |Col4  | Col5
  ----------------------------------
   hi   | this | is   | a    | test
  </code></pre>
  
  <p>Destination Table</p>
  
  <pre><code>RowValues|
  ----------
  hi
  this
  is
  a
  test
  </code></pre>
  
  <p>I am using Dynamic SQL.
  Any help ?</p>
  
  <p>This is my code , just change table name and the Id in the where clause to what suits you</p>
  
  <pre><code>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) 
  </code></pre>
  
  <p>Now I need to create a new table that has one column that hold holds each value as a row</p>
  
  <p>Thanks to @Mahmoud-Gamal
  The solution should be something like below </p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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 ?
  <strong>The type of column "Description" conflicts with the type of other columns specified in the UNPIVOT list.</strong></p>