4

I have a table containing columns

ID,
Variant (INT), 
A1.....A20 (VARCHAR(16))

Can anyone recommend the best method to shift the data in columns starting from X to right, where X is the parameter and column Variant equals to Y (the second parameter).

So, I would appreciate any help on the Stored Procedure taking X and Y as parameters and shifting the data to the right in columns starting from Ax, where Variant equals Y.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
user1727211
  • 51
  • 1
  • 2
    Welcome to Stackoverflow! Can you please include some rows of sample data and what you expect as the result given a particular X and Y? – RichardTheKiwi Oct 07 '12 at 19:49
  • Numbered columns are a SQL smell -- they typically indicate that your data is not properly normalized. –  Oct 07 '12 at 19:52
  • @user - we're really trying to help, so if you can edit your question with samples, it will help us understand you. For example you just added another constraint `providing the A20 does not contain any data`. What if it does? – RichardTheKiwi Oct 07 '12 at 20:03
  • You might be right with the normalization, but I need to put into the grid than and it's much easier to handle. It's just for my own use, so the flexibility is not needed. I have a grid/matrix containing simple strings. They are filled in manualy and if I made a mistake (forget one) I need to make a space for the missing (like insert column in Excel). – user1727211 Oct 07 '12 at 20:07
  • About A20, I will need to check this before starting the shifting and rise error. – user1727211 Oct 07 '12 at 20:09
  • What SQL product (and which version thereof) are you using? – Andriy M Oct 07 '12 at 21:14

1 Answers1

0

you could :

  1. create a new table from the existing one with the columns shifted :

    CREATE TABLE my_new_table AS SELECT A20,19... FROM original_Table;

  2. drop the original one

  3. rename this new one to the original table name
Alex Peta
  • 1,407
  • 1
  • 15
  • 26
  • Hi Alex,Thanks for your prompt response. However I would like to shift right the data only in some columns starting from Ax to A20 (providing the A20 does not contain any data) and Where Varaint = Y. – user1727211 Oct 07 '12 at 19:55
  • Someting like this. Basically I need this to insert a space (column) for the data: XXXXXXX XXXXXXX XXXXXXX XXX XXXX XXX XXXX XXX XXXX XXX XXXX – user1727211 Oct 07 '12 at 19:58
  • Dear All, I'm back in the same place of code. The SQL query I finlally going to use is like this: – user1727211 Dec 11 '12 at 16:32
  • Dear All, I'm back in the same place of code. The SQL query I'm finlally going to use is like this: 'UPDATE VariantTable SET A5 = A4, B5 = B4, A4 = A3, B4=B3, A3=null, B3=null SELECT A4, B4, A3, B3 FROM VariantTable WHERE VariantID = 2' the problem which I have now, is that if I run this code in Management Studio it works fine. However I want to run it from C# program and pass this string as parameter to tableAdapter. In this situation, exactly the same code fails with error: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. – user1727211 Dec 11 '12 at 16:42