June 4, 2014 at 2:33 pm
Trying to add a counter to names only when IDs are the same. My query adds counter to all names regardless if IDs are the same or not. I have includes both results of my query and the ideal one.
--Drop table ##Prov
CREATE TABLE ##prov
(
id VARCHAR(1),
NAME CHAR (20),
addresses VARCHAR (50)
)
INSERT INTO ##Prov
(id,name,addresses)
SELECT '1','john doe','50 north' UNION ALL
SELECT '1','john doe','70 north' UNION ALL
SELECT '2','jane doe','100 north'
SELECT P.*,
LTRIM(RTRIM(Name)) + RTRIM(LTRIM('-00'))+CAST(ROW_NUMBER() OVER (PARTITION BY id,name ORDER BY id,name ASC) AS VARCHAR) AS 'CounterName'
FROM ##Prov P
Results:
id NAME addresses CounterName
1 john doe 50 north john doe-001
1 john doe 70 north john doe-002
2 jane doe 100 north jane doe-001
Ideal Results:
id NAME addresses CounterName
1 john doe 50 north john doe-001
1 john doe 70 north john doe-002
2 jane doe 100 north jane doe
Thank You,
Helal
June 4, 2014 at 2:42 pm
You could try the following:
use a case statement. Do a count over the partition (just like you did with the row_number() function).
If the count is 1, do nothing. If the count is bigger than 1, assign the row number.
(this assumes you can use windowing functions though)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 4, 2014 at 2:42 pm
helal.mobasher 13209 (6/4/2014)
Results:id NAME addresses CounterName
1 john doe 50 north john doe-001
1 john doe 70 north john doe-002
2 jane doe 100 north jane doe-001
Ideal Results:
id NAME addresses CounterName
1 john doe 50 north john doe-001
1 john doe 70 north john doe-002
2 jane doe 100 north jane doe
As I read your requirements....if only one ID then do not append '-001'...is this correct?
if so, what are the benefits?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 4, 2014 at 2:59 pm
Not much benefit rather it's a requirement for the report...that if names are repeated with the same id, then add a counter.
June 4, 2014 at 3:28 pm
Kind of a unique requirement but not too bad. Just using the case suggestion from Koen. I also removed the LTRIM and RTRIM functions wrapping your constant -00 as that is not needed.
SELECT P.*,
Case when x.NameCount > 1 then
LTRIM(RTRIM(Name)) + '-00' + CAST(ROW_NUMBER() OVER (PARTITION BY id,name ORDER BY id,name ASC) AS VARCHAR)
else Name end AS 'CounterName'
, x.NameCount
FROM ##Prov P
cross apply (select count(NAME) as NameCount from ##prov p2 where p2.NAME = p.NAME) x
BTW, do you really need a global temp table? These have some issues with concurrency that temp tables do not have.
_______________________________________________________________
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/
June 5, 2014 at 7:38 am
Thank you all...worked great.
June 5, 2014 at 9:40 am
helal.mobasher 13209 (6/5/2014)
Thank you all...worked great.
Glad it worked for you. The question is do you understand what it is doing?
_______________________________________________________________
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 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply