Muhammad Reizvan Muhammad Reizvan - 6 months ago 14
SQL Question

SQL adding value at every comma separation

I have some query to sys.Columns that giving result :

DECLARE @queryRWordsSameType VARCHAR(500) ='[ItemCode],[ItemDesc],[ItemId],[ItemName]'


How do i do to make the value for each comma separated be like :

DECLARE @queryRWordsSameType VARCHAR(500) ='CAST([ItemCode] AS NVARCHAR(100)) AS [ItemCode]
,CAST([ItemDesc] AS NVARCHAR(100)) AS [ItemDesc]
,CAST([ItemId] AS NVARCHAR(100)) AS [ItemId]
,CAST([ItemName] AS NVARCHAR(100)) AS [ItemName]'

Answer

Here you go (though this is building the whole string directly from syscolumns):

DECLARE @queryRWordsSameType VARCHAR(500) = ''
SELECT @queryRWordsSameType += CASE WHEN @queryRWordsSameType <> '' THEN ',' ELSE '' END + 'CAST([' + name + '] AS NVARCHAR(100)) AS [' + name + ']' + CHAR(13) + CHAR(10)
  FROM syscolumns
 WHERE id = object_id('YOUR_TABLE_NAME')
PRINT @queryRWordsSameType