CODEWITHSUNDEEP
sql-servert-sqldynamicsql-server-2012concatenation
user3696556
user3696556

Reputation: 53

TSQL Concatenate values from multiple columns into string in one column

I have a trouble which seems possible to solve but I don't have the right idea now.

I have a table with multiple columns with dates in column names. These names will be changing so I probably need a dynamic code. Here is how the table looks like:

ID  2014-01-01 2014-01-02 2014-01-03 2014-01-04 2014-01-05 2014-01-06 (...) 2014-12-31
1    1             0           1          0          0          0               1
2    1             1           1          1          1          1               1                                      
3    1             1           0          1          1          1               0
4    1             0           0          1          1          1               1
5    1             1           0          0          0          1               1
(...)

So, there is a sequence of dates with logical values 0 or 1. What I need is to add another column to this set with a sequence of these values as a string like for example (for the ID = 1):

101000(...)1

As I mentioned, the dates can change.

Could you help me in that case ?

Upvotes: 0

Views: 1015

Answers (1)

PrivatePyle
PrivatePyle

Reputation: 61

I guess, you'll need dynamic SQL for that:

   DECLARE @sql NVARCHAR(MAX)
   DECLARE @tablename NVARCHAR(128) = 'FUNKTIONEN'

   SET @sql = (
   SELECT 'CONVERT(NVARCHAR(max),ISNULL('+ name + ','''')) + '
   FROM sys.all_columns
   WHERE object_id = (
       SELECT object_id
       FROM sys.all_objects
       WHERE object_id = object_id(@tablename)
       ) FOR     XML PATH (''))

   SET @sql = 'SELECT TOP 1 ' + LEFT(@sql,LEN(@sql)-1) + 'FROM ' + @tablename

   EXECUTE sp_executesql @sql

Upvotes: 1

Related Questions