September 22, 2017 at 12:31 pm
hi
i am using sql 2005.
table 1 structure
fk_id, line_number, mtr_value_100, mtr_value_102,mtr_value_200, mtr_value_201, etc............
table 2 structure
fk_id, value, line_number
i need to loop through the records, using the fk_id, line_number and then put the VALUE from table 2 into the correct column in table 1 base on the line_number, so each line_number corresponds to a particular colum number
so. cloumn mtr_value_100 gets the value from table 2 where line number is equal to 100
cloumn mtr_value_102 gets the value from table 2 where line number is equal to 1002
cloumn mtr_value_200 gets the value from table 2 where line number is equal to 200
so i have the follwing cursor and it works based on message output but it does not save the values in the columns
Declare @LeapId varchar(15)
Declare @RowNum int
Declare @columnname1 varchar(15)
Declare Leap_Cursor cursor for
--
Select distinct tag_line_number from stg_leap_lender_financials
--
OPEN Leap_Cursor
FETCH NEXT FROM Leap_Cursor
INTO @LeapId
set @RowNum = 0
WHILE @@FETCH_STATUS = 0
BEGIN
--
set @RowNum = @RowNum + 1
set @columnname1 = 'mtr_value_' + @LeapId
print cast(@RowNum as char(1)) + ' ' + @LeapId + ' ' + @columnname1
--
update t1
set @columnname1 = t.value ------ @columname1 = mtr_value_100, mtr_value_101, as it goes thru the loop
from oe_mf_staging..stg_leap_lender_financials t1, za_temp_leap_data t
WHERE
t1.date_ext_date = t.date_ext_date
and
t1.tag_line_number = @LeapId
and
t1.fk_id = t.fk_id
AND
t1.TAG_INST_ID = t.INST_ID
FETCH NEXT FROM Leap_Cursor
INTO @LeapId
END
CLOSE Leap_Cursor
DEALLOCATE Leap_Cursor
when i run the query in messages output i get
1 704 mtr_value_704
(2157 row(s) affected)
2 611 mtr_value_611
(2157 row(s) affected)
3 411 mtr_value_411
(1107 row(s) affected)
4 209 mtr_value_209
(2157 row(s) affected)
5 507 mtr_value_507
(2157 row(s) affected)
6 503 mtr_value_503
(2157 row(s) affected)
7 106 mtr_value_106
but when i do a select on the table all of the values in the columns are still NULL (the update did not take place)
Any help would be great
September 22, 2017 at 1:15 pm
Don't use a cursor for this. There are a couple of alternatives which are more efficient. You can read about them in these articles:
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - SQLServerCentral
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - SQLServerCentral
Here's an example on how your code would look like.
SELECT fk_id,
MAX( CASE WHEN line_number = 100 THEN value END) AS mtr_value_100,
MAX( CASE WHEN line_number = 102 THEN value END) AS mtr_value_102,
MAX( CASE WHEN line_number = 200 THEN value END) AS mtr_value_200,
MAX( CASE WHEN line_number = 201 THEN value END) AS mtr_value_201
--...
FROM stg_leap_lender_financials
GROUP BY fk_id;
September 22, 2017 at 1:26 pm
And here's an example on how to make it dynamic.
DECLARE @SQL nvarchar(max);
SELECT @SQL = N'SELECT fk_id'
+ ( SELECT ' ,MAX( CASE WHEN line_number = ' + CAST( line_number AS varchar(8))+ ' THEN value END) AS mtr_value_' + CAST( line_number AS varchar(8))
FROM stg_leap_lender_financials
GROUP BY line_number
ORDER BY line_number
FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')
+ NCHAR(10) + N'FROM stg_leap_lender_financials '
+ NCHAR(10) + N'GROUP BY fk_id;'
EXEC sp_executesql @SQL;
September 25, 2017 at 11:46 am
update t1
set @columnname1 = t.value ------ @columname1 = mtr_value_100, mtr_value_101, as it goes thru the loop
from oe_mf_staging..stg_leap_lender_financials t1, za_temp_leap_data t
It sort of looks like you're thinking @columnname1 will allow you to update the column that the name equals the value in the variable @columname1, but it doesn't update the column in the table, it updates the variable @columnname1. The old value of @columnname1 might be a string that is equal to an actual column name, but the update will not update the actual column, it will replace the actual column name that is currently contained in @columnname1 with a value that comes from the table oe_mf_staging..stg_leap_lender_financials.
Also, your update statement contains an implicit 92(?) era sql join, and there are legit cautions against its use.
Its better to structure your cross table updates as:
update t1
set t1.updateable column = t2.column_with_data_to_update_from
from sourcetable1 t1 join sourcetable2 t2 on t1.keycolumn = t2.keycolumn
ie., the join is explicitely specified.
September 25, 2017 at 12:23 pm
Thank you both the cross tab worked, have a great week.
September 25, 2017 at 3:58 pm
Luis Cazares - Friday, September 22, 2017 1:26 PMAnd here's an example on how to make it dynamic.
DECLARE @SQL nvarchar(max);
SELECT @SQL = N'SELECT fk_id'
+ ( SELECT ' ,MAX( CASE WHEN line_number = ' + CAST( line_number AS varchar(8))+ ' THEN value END) AS mtr_value_' + CAST( line_number AS varchar(8))
FROM stg_leap_lender_financials
GROUP BY line_number
ORDER BY line_number
FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')
+ NCHAR(10) + N'FROM stg_leap_lender_financials '
+ NCHAR(10) + N'GROUP BY fk_id;'
EXEC sp_executesql @SQL;
I just love that dynamic crosstab, Luis. Nicely done as always.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply