September 25, 2015 at 2:22 pm
Hi there,
I have to increment sequencial values for the following:
Current Data
Col1 Col2 Col3
12.345.678 0001 32
13.456.789 0002 43
Updated Data
Col1 Col2 Col3
12.345.678 0001 32
12.345.678 0002 32
12.345.678 0003 32
13.456.789 0002 43
13.456.789 0003 43
13.456.789 0004 43
What I need is: Increment up to 3 times the values in Col2. Need to identify the first number in Col2 and increase the number up to 3.
Thanks in advance for your help.
September 25, 2015 at 2:56 pm
eric-silva (9/25/2015)
Hi there,I have to increment sequencial values for the following:
Current Data
Col1 Col2 Col3
12.345.678 0001 32
13.456.789 0002 43
Updated Data
Col1 Col2 Col3
12.345.678 0001 32
12.345.678 0002 32
12.345.678 0003 32
13.456.789 0002 43
13.456.789 0003 43
13.456.789 0004 43
What I need is: Increment up to 3 times the values in Col2. Need to identify the first number in Col2 and increase the number up to 3.
Thanks in advance for your help.
Try the following:
WITH RAW_DATA AS (
SELECT '12.345.678' AS Col1, '0001' AS Col2, 32 AS Col3 UNION ALL
SELECT '13.456.789' AS Col1, '0002' AS Col2, 43 AS Col3
),
E1 AS (
SELECT *
FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) AS X(N)
),
Tally AS (
SELECT ROW_NUMBER() OVER(ORDER BY E1.N) AS RN
FROM E1, E1 AS E2, E1 AS E3, E1 AS E4
)
SELECT RD.Col1, RIGHT('000' + CAST(T.RN AS varchar(4)), 4) AS Col2, RD.Col3
FROM RAW_DATA AS RD
CROSS APPLY Tally AS T
WHERE T.RN BETWEEN RD.Col2 AND RD.Col2 + 2
ORDER BY RD.Col1, RIGHT('000' + CAST(T.RN AS varchar(4)), 4);
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 25, 2015 at 3:02 pm
eric-silva (9/25/2015)
Hi there,I have to increment sequencial values for the following:
Current Data
Col1 Col2 Col3
12.345.678 0001 32
13.456.789 0002 43
Updated Data
Col1 Col2 Col3
12.345.678 0001 32
12.345.678 0002 32
12.345.678 0003 32
13.456.789 0002 43
13.456.789 0003 43
13.456.789 0004 43
What I need is: Increment up to 3 times the values in Col2. Need to identify the first number in Col2 and increase the number up to 3.
Thanks in advance for your help.
Hi and welcome to the forums. Your post is lacking most of the details required to help. Next time you need to post details for us.
For a great place to start with what information you should post please take a look at this link. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/[/url]. Alternately you can look at the first link in my signature for best practices when posting questions.
As luck would have it I was able to decipher your request...as least I think so.
Something like this should work for you.
create table #Something
(
Col1 varchar(15)
, col2 varchar(5)
, col3 int
)
insert #Something
select '12.345.678', '0001', 32 union all
select '13.456.789', '0002', 43
select s.Col1
, RIGHT('000' + cast(cast(col2 as int) + n as varchar(2)), 4) as col2
, col3
from #Something s
cross apply (values(1), (2), (3)) x(n)
_______________________________________________________________
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/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply