November 22, 2010 at 2:20 pm
Hi my data is
Id amount
213 500
214 600
215 700
215 400
215 550
216 300
216 600
217 500
I am looking for output like below
Id amount
213 500
214 600
21501 700
21502 400
21503 550
21601 300
21602 600
217 500
if same id repetes then it has to add 01,02,03 after the id
is it possible
November 22, 2010 at 2:25 pm
Would adding a row_number() column (that's a function) do what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 22, 2010 at 4:04 pm
GSquared (11/22/2010)
Would adding a row_number() column (that's a function) do what you need?
@shyaagi: The row_number() approach requires to specify a column that would define the order.
Unfortunately, I cannot find an easy pattern that can be used to order the data in your required output. You might need to add a column that would help to specify the order.
One (rather weird) way would be though:
ROW_NUMBER() OVER(PARTITION BY Id ORDER BY
CASE WHEN amount>= 700 THEN 0 ELSE 1 END, amount)
November 22, 2010 at 11:14 pm
DECLARE @ThisWouldHaveBeenHelpful
TABLE (
Id INTEGER NOT NULL,
Amount INTEGER NOT NULL
);
INSERT @ThisWouldHaveBeenHelpful
(Id, Amount)
VALUES (213, 500),
(214, 600),
(215, 700),
(215, 400),
(215, 550),
(216, 300),
(216, 600),
(217, 500);
WITH NumberingAndCounting
AS (
SELECT *,
rn = ROW_NUMBER() OVER (PARTITION BY H.Id ORDER BY H.Id),
cnt = COUNT_BIG(*) OVER (PARTITION BY H.Id)
FROM @ThisWouldHaveBeenHelpful H
)
SELECT Id = CASE WHEN N.cnt > 1 THEN N.Id * 100 + N.rn ELSE N.Id END,
N.Amount
FROM NumberingAndCounting N;
This works, but isn't very satisfactory. The problem, as Lutz hinted, is that the only way to determine which row 'follows' which (for the numbering sequence) is to look at the order in which you presented the data - i.e. in written order. In general, SQL Server has no in-built ordering for rows, so in your real-world table one would hope such an ordering column exists.
It's all a bit academic anyway since I cannot conceive of why anyone would legitimately need to do this sort of nonsense in SQL Server anyway. A puzzle is a puzzle, I suppose.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 23, 2010 at 12:16 am
Hello,
You can use row_number and count with partition by specification.
In the below example I used sql pad leading zeros function during id and seq.number concatenation
with cte as (
select
id,
amount,
rn = ROW_NUMBER() over (PARTITION BY Id Order BY ID),
cnt = COUNT(*) over (PARTITION BY Id)
from table2
)
select
CASE
WHEN cnt = 1 THEN Id
ELSE
cast(id as varchar(10)) + cast(dbo.udfLeftSQLPadding(rn,2,'0') as varchar(10))
END,
amount
from cte
November 23, 2010 at 12:49 am
Eralper (11/23/2010)
In the below example I used sql pad leading zeros function during id and seq.number concatenation
Why use a row-by-row T-SQL scalar function? There's also a horrible hidden cast from BIGINT (rn) to NVARCHAR(MAX) (your function's parameter). If you really must have functions like this in your repertoire, use a more efficient in-line version:
CREATE FUNCTION
dbo.LeftPad
(
@String NVARCHAR(4000),
@Length INT,
@PaddingChar CHAR(1) = '0'
)
RETURNS TABLE
RETURN
SELECT padded = RIGHT(REPLICATE(@PaddingChar, @Length) + @String, @Length);
Overall, I don't see what value your reply added to mine.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 23, 2010 at 12:55 am
Hi Paul,
Actually your previous solution does not even require padding.
That is a better solution I guess for this example.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply