SQL Server Performance

Attempting to create new column that includes data from two other columns

Discussion in 'SQL Server 2005 Integration Services' started by Bryanens, Jan 16, 2008.

  1. Bryanens New Member

    I am attempting to create a new column "data values" from two other columns of variable 1 and variable 2 yet I want all other information in those columns to be constant...
    example:
    time place var1 var2
    1 home 4.5 3.4
    2 away 5.6 3.0
    should be
    time place datavalue var#
    1 home 4.5 1
    1 home 3.4 2
    2 away 5.6 1
    2 away 3.0 2
    Any suggestions? I tried to do this with scripts but cannot seem to learn how to alternate the datavalue column between two variables.
    Help would be greatly appreciated!
  2. Madhivanan Moderator

    Try
    select time,place,datavalue, row_number() over(partition by time order by time) as sno from
    (
    select time , place , var1 as datavalue from table
    union all
    select time , place , var2 from table
    ) as t


  3. dineshasanka Moderator

    Better if you can post it at correct forum
  4. Adriaan New Member

    Or simply ...
    SELECT t.time, t.place, var1 AS datavalue, 1 AS [var#] FROM MyTable
    UNION ALL
    SELECT t.time, t.place, var2, 2 FROM MyTable
    ORDER BY 1, 2, 4
  5. Madhivanan Moderator

    [quote user="Adriaan"]
    Or simply ...
    SELECT t.time, t.place, var1 AS datavalue, 1 AS [var#] FROM MyTable
    UNION ALL
    SELECT t.time, t.place, var2, 2 FROM MyTable
    ORDER BY 1, 2, 4
    [/quote]
    Good one which will work for all versions of SQL Server [:)]
    But I think you forget to specify alias table name t
  6. Adriaan New Member

    Yep - perhaps a little too quick to post that, here it is:
    SELECT t.time, t.place, t.var1 AS datavalue, 1 AS [var#] FROM MyTable t
    UNION ALL
    SELECT t.time, t.place, t.var2, 2 FROM MyTable t
    ORDER BY 1, 2, 4
  7. ScottPletcher New Member

    SELECT time, place, CASE WHEN var# = 1 THEN var1 ELSE var2 END AS datavalue, var#
    FROM tablename
    CROSS JOIN (
    SELECT 1 AS var# UNION ALL
    SELECT 2
    ) AS whichVars
    ORDER BY 1, 2, 4 --optional, of course [:)]
  8. satya Moderator

    Scott
    good to see you here, hope to get more in this regard [:)]...welcome back.
  9. Bryanens New Member

    I'm still having the same problem. It seems as I should have made myself more clear and am doing this entire process in ssis scripts and importing the data from a flat file. The central problem I am having is I am unable to have manipulate more than one row at a time. For example if I try to write a dataID row and simply want a straight count for each row of data I have by writing
    dim i as integer = 1
    row.dataID = i
    i=i+1
    It just returns i= 1 and does not update in the following row. I figure there is a simple solution to this but again I am quite new to ssis. Thanks

Share This Page