I'm stumped on a concatenation problem

  • kdfischer

    SSC Veteran

    Points: 217

    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

    1 KDF 987654321 FISCHER KEVIN 62.50 0.00 1 3 0000/0000/0000/0000

    2 KDF 987654321 FISCHER KEVIN 0.00 62.50 47 4 0000/0000/0000/0000

    3 KDF 123456789 FISCHER MELISSA 21.00 0.00 1 3 0000/0000/0000/0000

    4 KDF 123456789 FISCHER MELISSA 0.00 21.00 50 5 0000/0000/0000/0000

    5 KDF 135791357 WILLIAMS CALEB 10.00 0.00 1 4 0000/0000/0000/0000

    6 KDF 135791357 WILLIAMS CALEB 0.00 10.00 1 5 0000/0000/0000/0000

    What I need is to update #tmp1 to be this:

    1 KDF 987654321 FISCHER KEVIN 62.5 0 1 3 0001/0047/0000/0000

    2 KDF 987654321 FISCHER KEVIN 0 62.5 47 4 0001/0047/0000/0000

    3 KDF 123456789 FISCHER MELISSA 21 0 1 3 0001/0000/0050/0000

    4 KDF 123456789 FISCHER MELISSA 0 21 50 5 0001/0000/0050/0000

    5 KDF 135791357 WILLIAMS CALEB 10 0 1 4 0000/0001/0001/0000

    6 KDF 135791357 WILLIAMS CALEB 0 10 1 5 0000/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.

  • Evil Kraig F

    SSC Guru

    Points: 100851

    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.


    - Craig Farrell

    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

  • Sean Lange

    SSC Guru

    Points: 286515

    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/

  • Evil Kraig F

    SSC Guru

    Points: 100851

    You're right Sean, apologies are above. That's also a much prettier method then the pivot I was thinking of.


    - Craig Farrell

    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

  • Sean Lange

    SSC Guru

    Points: 286515

    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/

  • kdfischer

    SSC Veteran

    Points: 217

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

  • Sean Lange

    SSC Guru

    Points: 286515

    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/

  • drew.allen

    SSC Guru

    Points: 76705

    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

  • Sean Lange

    SSC Guru

    Points: 286515

    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/

  • ACinAZ

    SSCommitted

    Points: 1849

    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 10 (of 10 total)

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