September 26, 2011 at 3:15 pm
I have the following:
create table #tmp1
(IDKEY INT IDENTITY, div nvarchar(3), ID nvarchar(10), LastName nvarchar(20), FirstName nvarchar(20),
amt1 numeric(5,2), amt2 numeric(5,2), accumid nvarchar(20), prodcd nvarchar(20), workaccumid nvarchar(max))
insert into #tmp1 select 'KDF', '987654321', 'FISCHER', 'KEVIN', 62.50, 0, '1', '3', null
insert into #tmp1 select 'KDF', '987654321', 'FISCHER', 'KEVIN', 0, 62.50, '47', '4', null
insert into #tmp1 select 'KDF', '123456789', 'FISCHER', 'MELISSA', 21.00, 0, '1', '3', null
insert into #tmp1 select 'KDF', '123456789', 'FISCHER', 'MELISSA', 0, 21.00, '50', '5', null
insert into #tmp1 select 'KDF', '135791357', 'WILLIAMS', 'CALEB', 10.00, 0, '1', '4', null
insert into #tmp1 select 'KDF', '135791357', 'WILLIAMS', 'CALEB', 0, 10.00, '1', '5', null
This gives me:
ID Div SSN Last First Amt1 Amt2 AccID ProdCd
1KDF987654321FISCHER KEVIN62.500.00130000/0000/0000/0000
2KDF987654321FISCHER KEVIN 0.0062.504740000/0000/0000/0000
3KDF123456789FISCHER MELISSA21.000.00130000/0000/0000/0000
4KDF123456789FISCHER MELISSA 0.0021.005050000/0000/0000/0000
5KDF135791357WILLIAMSCALEB10.000.00140000/0000/0000/0000
6KDF135791357WILLIAMSCALEB 0.0010.00150000/0000/0000/0000
What I need is to update #tmp1 to be this:
1KDF987654321FISCHER KEVIN62.50130001/0047/0000/0000
2KDF987654321FISCHER KEVIN062.54740001/0047/0000/0000
3KDF123456789FISCHER MELISSA210130001/0000/0050/0000
4KDF123456789FISCHER MELISSA0215050001/0000/0050/0000
5KDF135791357WILLIAMSCALEB100140000/0001/0001/0000
6KDF135791357WILLIAMSCALEB010150000/0001/0001/0000
Notice the last column is the same for all similar people. So, in other words, it's all the values of the same person concatenated but added to each row. The 1st 4 positions are the value of AccumID if Prodcd = '3', pos. 6-9 are AccumID if ProdCd = '4', pos. 11-14 are AccumID if ProdCd = '5', and pos. 16-19 are AccumID if prodcd = '11'.
I for the life of me cannot figure out how to update this table to do this. Any help is appreciated.
September 26, 2011 at 3:35 pm
You need a pivot.
If you'll setup the data and schema to be consumable, I'll actually plug away at the code when I get some time to get it to behave. You'll see what most of us are looking for in the first link in my signature. Others will find it easier to get here ahead of me with that, as well... and you'll even get tested code!
EDIT: I'm a blind fool, please accept my apologies. Started to write an answer, got distracted, and apparently I typed in the wrong window.
Thank you Sean.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 26, 2011 at 3:38 pm
Evil Kraig F (9/26/2011)
You need a pivot.If you'll setup the data and schema to be consumable, I'll actually plug away at the code when I get some time to get it to behave. You'll see what most of us are looking for in the first link in my signature. Others will find it easier to get here ahead of me with that, as well... and you'll even get tested code!
Actually he did post the ddl and sample data quite nicely. His explanation was a bit off and took several times before i started to get what he is after.
I think the following will work given your sample data and the business rules stated.
update #tmp1
set workaccumid = NewValue
from #tmp1
join
(
select ID,
right('0000' + cast(MAX(case prodcd when 3 then accumid else 0 end) as varchar(4)), 4) + '/' +
RIGHT('0000' + cast(max(case prodcd when 4 then accumid else 0 end) as varchar(4)), 4) + '/' +
RIGHT('0000' + cast(max(case prodcd when 5 then accumid else 0 end) as varchar(4)), 4) + '/' +
RIGHT('0000' + cast(max(case prodcd when 11 then accumid else 0 end) as varchar(4)), 4) as NewValue
from #tmp1
group by id
) vals on vals.ID = #tmp1.ID
select * from #tmp1
Let me know if that works for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 26, 2011 at 4:32 pm
You're right Sean, apologies are above. That's also a much prettier method then the pivot I was thinking of.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 27, 2011 at 7:06 am
kdfischer - Did that get you what you needed or do you still need some help with this?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 27, 2011 at 7:46 am
I believe it did. Testing things out.
I was on the right track. I seem to always struggle on more than a simple UPDATE.
Thank you!!!
September 27, 2011 at 8:17 am
You are welcome. Thanks for letting us know you got a solution.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 27, 2011 at 8:32 am
The only thing that I would suggest is that since AccumID is already character data, it doesn't make sense to (implicitly) convert it to an integer and then (explicitly) convert it back to character. You do have to be careful with the MAX() for character data, but that should be resolved by moving the RIGHT() inside of the MAX().
Max( Right('0000' + CASE ProdCd WHEN 3 THEN AccumID ELSE '' END, 4 )) + '/'
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 27, 2011 at 8:44 am
drew.allen (9/27/2011)
The only thing that I would suggest is that since AccumID is already character data, it doesn't make sense to (implicitly) convert it to an integer and then (explicitly) convert it back to character. You do have to be careful with the MAX() for character data, but that should be resolved by moving the RIGHT() inside of the MAX().
Max( Right('0000' + CASE ProdCd WHEN 3 THEN AccumID ELSE '' END, 4 )) + '/'
Drew
Good point Drew, but you left an implicit conversion inside the case...
Max( Right('0000' + CASE ProdCd WHEN '3' THEN AccumID ELSE '' END, 4 )) + '/'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 28, 2011 at 9:14 am
Why store this in the table at all? If the "feeder" columns ever change, then you'll have to run the update for it again. Why not leave it out, then derive the values whenever you actually need to display or report it?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply