Can I rename data passing through a query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Can I rename data passing through a query?

Author

Topic

wokflop
Starting Member

2 Posts

Posted - 2010-02-24 : 06:47:40

Not to sure if this is possible, I have a query giving me two columns "Product" and "Total" but the product is in number form.

this is what i have so far.

SELECT product AS Product, COUNT(*) AS Total
FROM leakers
GROUP BY product

Prod Total
41234 10
62521 34
76352 99

I'm looking for something to say IF PRODUCT = 1234 THEN PRODUCT = ABCD within a query.

Prod Total
ABCD 10
EFTS 34
UYAT 99

The database data needs to remain in unchanged.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-24 : 07:06:32

select
case Product
when 1234 then 'ABCD'
when 4321 then 'DCBA'
...
else 'unknown'
end as Prod,
count(*) as Total
from leakers
group by
case Product
when 1234 then 'ABCD'
when 4321 then 'DCBA'
...
else 'unknown'
end

No, you're never too old to Yak'n'Roll if you're too young to die.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-24 : 07:13:05

I suppose wokflop wants the query where product name is 1234 then abcd 2312 then bcab 5678 efgh like that...

Vabhav T

wokflop
Starting Member

2 Posts

Posted - 2010-02-24 : 07:47:38

Thank for that webfred! You have saved my hair and made it to my christmas card list.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-24 : 07:50:02

quote:
Originally posted by vaibhavktiwari83

I suppose wokflop wants the query where product name is 1234 then abcd 2312 then bcab 5678 efgh like that...

Vabhav T

Webfred's query is just doing it

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-24 : 07:58:07

my pleasure

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-24 : 08:01:39

quote:
Originally posted by vaibhavktiwari83

I suppose wokflop wants the query where product name is 1234 then abcd 2312 then bcab 5678 efgh like that...

Vabhav T

Better you put data in lookup table and join with it. This way you dont need to change the code if new codes are added

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-24 : 08:09:29

Might specific problem has been solved but
i wasw working with if any body want to replace 1 with a, 2 with b and so on in a perticular string of numbers then what?

Vabhav T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-24 : 08:16:12

quote:
Originally posted by vaibhavktiwari83

Might specific problem has been solved but
i wasw working with if any body want to replace 1 with a, 2 with b and so on in a perticular string of numbers then what?

Vabhav T

What do you want to replace for 0?

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-24 : 08:45:25

I will keep as it is even if one string is like that '12A34' THEN that should be 'ABACD'
means i am replacing only character from 1 to 9 with 'A' to 'I'

Vabhav T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-24 : 08:46:45

quote:
Originally posted by vaibhavktiwari83

I will keep as it is even if one string is like that '12A34' THEN that should be 'ABACD'
means i am replacing only character from 1 to 9 with 'A' to 'I'

Vabhav T

What is the need for this?

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-24 : 08:50:34

Are you doing all the exercise in this forum for the need only.

Vabhav T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-24 : 08:55:16

quote:
Originally posted by vaibhavktiwari83

Are you doing all the exercise in this forum for the need only.

Vabhav T

Yes. Tell me if there is a need to do this?

Are you doing this as an exercise?

You need to extract each digit and replace with alphabet. Create a function for this and call it from the SELECT statement

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-24 : 08:57:03

yes that is what i have done...

CREATE FUNCTION ConvertText( @str varchar(10) )
RETURNS VARCHAR(10)
AS
BEGIN

DECLARE @tmpstr as varchar(10), @cnvstr as varchar(10)
set @tmpstr = @str
SET @cnvstr = ''

WHILE len(@tmpstr) > 0
BEGIN
SET @cnvstr = @cnvstr + CASE WHEN substring(@tmpstr, len(@tmpstr), 1) IN ('1', '2', '3', '4', '5', '6', '7', '8', '9') THEN CHAR( substring(@tmpstr, len(@tmpstr), 1) + 64 ) ELSE substring(@tmpstr, len(@tmpstr), 1) END
SET @tmpstr = substring(@tmpstr, 1, len(@tmpstr)-1 )
END
RETURN REVERSE(@cnvstr)
END

SELECT ConvertText(product) AS Product, COUNT(*) AS Total
FROM leakers GROUP BY product

Vabhav T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-24 : 09:08:46

You can simplify the function like this


CREATE FUNCTION ConvertText( @str varchar(10) )
RETURNS VARCHAR(10)
AS
BEGIN 

declare @s varchar(10), @data varchar(10)
select @s=@str, @data=''
while @s>''
begin
	select @data=@data+char(left(@s,1)+64), @s=substring(@s,2,len(@s))
end
return (@data)
end

select dbo.convertText('98423')

SELECT ConvertText(product) AS Product, COUNT(*) AS Total
FROM leakers GROUP BY product

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-24 : 09:31:25

Yes, thanks madhi

I always blow with the logic instead of looking at the code thats why always it happens...

Vabhav T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-24 : 09:48:24

quote:
Originally posted by vaibhavktiwari83

Yes, thanks madhi

I always blow with the logic instead of looking at the code thats why always it happens...

Vabhav T

No problem. You will learn more in the coming days

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources