I have to shift 20+ columns over one to the left, for a set of rows. Is there a way to do this without manually typing each column name? I know how to get the data for the specific rows.

For example: move data in column q49 to q48. move data in column q50 to q49 and so on up to column q79.

I know how to move data, I just wondered how I could write it so I wouldn't have to type the long set list in an update statement:

update tablename
set q48 = q49, q49 = q50, q50 = q51, ...
from ...
where ...

I assume there would be a way to use a variable for the column names but I could be wrong. How can this be resolved?

  • 2
    Just type them. It would take you less time than typing the question. And much more less than writing and testing a dynamic SQL procedure that achieves what you want. – ypercubeᵀᴹ Apr 16 '18 at 8:22
  • it's not even clear what you want to be generated. 20+ and the only ones you start at are q50 to q49?.. Also that's not how you DB. – Evan Carroll Apr 16 '18 at 20:04

As far as SQL Server is concerned, column names must be specified explicitly. There is no way around. As far as you are concerned, names can be parametrised, but that means you need to generate the entire statement dynamically.

Depending on how often you need to do this, there may be no need to use dynamic SQL. If this is a one-off job, you could just generate the assignment list using something like

  x.prev_name + ' = ' + c.name + ','
  sys.columns AS c
  CROSS APPLY (SELECT CONCAT('q', STUFF(c.name, 1, 1, '') - 1)) AS c (prev_name)
  c.object_id = OBJECT_ID('dbo.tablename')
  AND c.name BETWEEN 'q49' AND 'q79'

Run that in SSMS to get an output like this:

(No column name)
q48 = q49,
q49 = q50,
q50 = q51,
q78 = q79,

Copy the contents of the output column and paste it into the query window where you are writing your UPDATE statement, at the appropriate position. You will only need to make minimal syntactical adjustments, like removing the comma after the final assignment.

Even if I knew I might need to do this kind of data movement again in the future, I would still consider this approach first before actually going the dynamic SQL route.

If you firmly believe you need a more convenient tool for this problem, then you can use the above statement as a starting point for your script. Browse the questions on this site or on Stack Overflow to find more examples to help you with the final solution.

Not the answer you're looking for? Browse other questions tagged or ask your own question.