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!
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
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 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
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
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 []
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