July 22, 2009 at 12:42 pm
I have a table of values that I am importing to my database. The data is coming in as:
ParticipantIDRowID GenderAgeIsUserIsEligible
1 1 m32 Y Y
2 3 f40 n N
3 15 M30 Y Y
and actually, there are about 100 different columns in the data.
I need to take most of the columns and store one record per column per participant. While I do this, i also need to do some conversions, like converting 'y' to TRUE, etc. e.g.
ParticipantIDRowID MeasureValue
11Genderm
11Age32
11IsUserTRUE
11IsEligibleTRUE
23Genderf
23Age40
23IsUserFALSE
23IsEligibleFALSE
etc...
I have a table of conversions, that tell me how to convert each value, but I don't know how to cycle through the records and pick up the columns without hardcoding each one. :w00t:
DECLARE dataimport_cursor CURSOR FOR
SELECT * FROM dataImport
OPEN dataimport_cursor
FETCH NEXT FROM dataimport_cursor
WHILE @@FETCH_STATUS = 0
/* get columns for import table */
DECLARE usr_cursor CURSOR FOR
SELECT [Column_name] FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'dataImport'
OPEN usr_cursor
FETCH NEXT FROM usr_cursor
INTO @COLUMN
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @MEASURE_TYPE from MeasureConversion Where MeasureField = @COLUMN
IF @MEASURE_TYPE = 'coded'
/* get field value to lookup and convert */
BEGIN
[highlight=#ffff11] /* how do i get the data value from the current dataimport record for the current colum in @COLUMN? */[/highlight]
END
IF @MEASURE_TYPE = 'value'
/* write output record */
BEGIN
[highlight=#ffff11] /* how do i get the data value from the current dataimport record for the current colum in @COLUMN? */[/highlight]
INSERT ...
END
FETCH NEXT FROM usr_cursor
INTO @COLUMN
END
CLOSE usr_cursor
DEALLOCATE usr_cursor
FETCH NEXT FROM dataimport_cursor
END
CLOSE dataimport_cursor
DEALLOCATE dataimport_cursor
Thanks for any and all help!
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply