TSQL Concatenate values from multiple columns into string in one column

Question

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 ?

To summarize - you need code that concatenates row data into a single colum (for different number of columns and with different names)? — Bogdan Bogdanov
– Bogdan Bogdanov, Commented Sep 7, 2015 at 8:05
This smells a little bit like an XY problem. If you don't mind, what are you planning to do with that string once you've got it? — lc.
– lc., Commented Sep 7, 2015 at 8:07
I agree with @lc. This seems like a bad design for a table. I would suggest changing to a table of dates, a table of ids, and a table for values (allowing many to many relationship between dates and ids. — Zohar Peled
– Zohar Peled, Commented Sep 7, 2015 at 8:09

PrivatePyle · Accepted Answer · 2015-09-07 10:28:41Z

1

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

answered Sep 7, 2015 at 10:28

PrivatePyle

615 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

TSQL Concatenate values from multiple columns into string in one column

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related