tsql to store converted values

Question

Post reply

tsql to store converted values

lpatrick

SSC Rookie

Points: 48
More actions
July 22, 2009 at 12:23 pm

#133209

I have a table of values that I am importing to my database. The data is coming in as:
ParticipantIDRowID GenderAgeIsUserIsEligible
11m32YY
23f40nN
315M30YY
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, e.g.
ParticipantIDRowID MeasureValue
11Genderm
11Age32
11IsUserTRUE
11IsEligibleTRUE
23Genderf
23Age40
23IsUserFALSE
23IsEligibleFALSE
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.
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
END
IF @MEASURE_TYPE = 'value'
/* write output record */
BEGIN
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

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Look at PIVOT in Books Online. I think that might be what you are looking for..

CEWII

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Elliott W (7/22/2009)
Look at PIVOT in Books Online. I think that might be what you are looking for..
CEWII

Actually, I think you want UNPIVOT.

lpatrick SSC Rookie Points: 48 More actions · Answer 3

Thanks for the quick response!

My next question is how I get the column name to put in the 'unpivoted' data.

And nextly (I think this is an easier one), it looks like pivot provides the data to the program, then do I take it and 'insert' into a new table row by row?

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 4

lpatrick (7/22/2009)
Thanks for the quick response!
My next question is how I get the column name to put in the 'unpivoted' data.
And nextly (I think this is an easier one), it looks like pivot provides the data to the program, then do I take it and 'insert' into a new table row by row?

My recommendation would be to first lookup "unpivot" in Books Online and select the "Using Pivot and Unprivot" subject. Yes, the output of the SELECT for unpivot can be used as part of an INSERT/SELECT statement and you won't need to do it in a RBAR fashion. You can write a select to help you create all the column names for an unpivot query by querying INFORMATION_SCHEMA.Columns.

Lemme see what I can do... I'll try to get back in a bit...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 5

On second thought, UNPIVOT is a real PITA to code dynamically. Here's a much simpler method that you can use. I've got it setup to do an existing table. You'll need to make some minor modifications to make it work on your table. Read the comments and the object names to figure out what the changes should be. I'd have set it up for your table except you didn't provide the table creation statement. And, look Ma... no cursor... (gee... I wonder why they say set based code is so much more difficult than a cursor... this is some short code!)...

--===== Declare some Dynamic SQL variables

DECLARE @SqlInsert VARCHAR(MAX),

@SqlSelect VARCHAR(MAX)

--===== This is the datatype of the [Value] column in the target table

DECLARE @DataTypeOfValue VARCHAR(32)

SELECT @DataTypeOfValue = 'NVARCHAR(60)'

--===== This is the INSERT part of the dynamic SQL

SELECT @SqlInsert = 'INSERT INTO dbo.YourNvpTable (PK, Measure, [Value])' + CHAR(10)

--===== This makes up the SELECT/UNION ALL part of the INSERT of the dynamic SQL.

-- I took the easy but slower way out on the concatenation for this one time code

-- because it's late.

SELECT @SqlSelect = ISNULL(@SqlSelect + ' UNION ALL' + CHAR(10),'')

+ 'SELECT AddressID,'

+ QUOTENAME(Column_Name,'''') + ' AS Measure,'

+ 'CAST(' + Column_Name + ' AS ' + @DataTypeOfValue + ') AS [Value] '

+ 'FROM Person.Address'

FROM Information_Schema.Columns

WHERE Table_Schema = 'Person'

AND Table_Name = 'Address'

AND Column_Name NOT IN ('AddressID','RowGuid','ModifiedDate')

--===== Change the PRINT to EXEC() when you're ready

PRINT @SqlInsert + @SqlSelect

-- EXEC (@SqlInsert + @SqlSelect)

Before anyone jumps on me... if it weren't so late, I'd have used the slightly more complicated XML form of concatenation.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 6

Any feedback on this problem?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lpatrick SSC Rookie Points: 48 More actions · Answer 7

Jeff,

While I've been programming for years, I'm relatively new to TSQL. (Give me a good asp.net page to code). So I took some very significant pointers from your code and have my code working 99 percent.

That said, i'm still using a cursor, as I tried to totally understand your code, tried to execute it substituting my tables and failed. I'm not sure what the 'union' does and not sure why you are selecting AddressID.

Once I get my code fully working in my clunky method, I'll try to enhance it to utilize even more of the concept. I tend to learn in stepwise fashing.

Do you guys do this for fun (in lieu of crossword puzzles), or fame, or ?? (I'm assuming there's no monetary reward). It's really great to get this kink of feedback.

Thank you for taking the time to do this!

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 8

In my example, AddressID would be the same as your two columns, ParticipantID and RowID.

On your other question, while some bit of unexpected notoriety has come of it, I do it because I remember what it was like when I started in SQL... I didn't have the Internet way back then and the answers I got to my questions from others were pretty shallow. When I asked questions like "How do I eliminate duplicates?", the "pros" of the time couldn't help and simply stated (over and over), "If you've done everything right, you won't have duplicates." It was a bit frustrating.

I'm on my way to work... didn't know you were brand new to T-SQL... I'll try to give you a better explanation of the code tonight.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 9

Almost forgot... it would be a help if you attached the CREATE TABLE statment for your table as a text file. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)