This make the job.
declare @tableName varchar(30) = 'YourTable';
declare @columnNames table(name varchar(30));
insert into @columnNames select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName
declare @dynamicCommand varchar(max) = 'select '
declare @columnName varchar(30)
declare columnNames_cursor cursor
FOR
select name from @columnNames
Open columnNames_cursor
FETCH NEXT FROM columnNames_cursor into @columnName
while @@fetch_status = 0
Begin
--print @columnName + ' : '
set @dynamicCommand = @dynamicCommand + '''' + @columnName + ''' + '' '' + convert(nvarchar,' + @columnName + '), '
FETCH NEXT FROM columnNames_cursor into @columnName
End
Close columnNames_cursor
Deallocate columnNames_cursor
-- remove last ', '
set @dynamicCommand = Substring( @dynamicCommand, 0, len (@dynamicCommand))
print @dynamicCommand
set @dynamicCommand = @dynamicCommand + ' from ' + @tableName
EXEC ( @dynamicCommand)
Sorry, but I do not yet figure out why you have to do this.
If I have to do something like this (dynamic tables) I prefer make the work by code, firstly obtaining the columns from table schema (with SQL type and nullability) and then dynamically creating the query for the data.
Then, knowing "structure" and data I can perform all kind of operation/formatting.
If my example is good for you I ask you to test for NULL value, varchar + NULL return NULL, if I remember correctly. I think the
IsNull function can solve this, in case.
For the first purpose .Net have all included facility (GetSchema(@tableName)), but for COBOL I think at this:
SELECT
ORDINAL_POSITION
,COLUMN_NAME
,DATA_TYPE
,CHARACTER_MAXIMUM_LENGTH
,IS_NULLABLE
,COLUMN_DEFAULT
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = @tableName
ORDER BY
ORDINAL_POSITION ASC;
[Added]
My example returns [NameColumn1 ValueColumn1], [NameColumn2 ValueColumn2] ...
To obtain result in the reverse order order (as your example) [ValueColumn1 NameColumn1] ecc...
use this (I confirm my previous doubt about NULL value with string concatenation) with
IsNull applied:
set @dynamicCommand = @dynamicCommand + 'convert(nvarchar, IsNull(' + @columnName + ', ''''))' + ' + '' '' + ''' + @columnName + ''', '
(those are ALL single quote)
You can also substitute the final ', ' with ''' ''' to obtain a unique long string exactly as your example in first request, or add more to separate data columns from column name columns.
