CASE functionality. - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

CASE functionality.

Author

Topic

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2009-07-22 : 01:55:33

Hi All,

I have two fields in table. I want to fetch the details from table for some formating using CASE--WHEN--THEN functions.

My requirement is below.

The table contains.

AccessId Read Add Edit Delete
1 1 1 1 1
2 1 0 0 0
3 0 1 1 0

I want to get the result as below..

AccessId Rights
1 R/A/E/D ---> Because Read-1, Add-1, Edit-1 and Delete-1
2 R
3 A/E

How can we achive this result from query. Pls help me,

Regards,
Kamal.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-22 : 01:58:27

[code]
select AccessId,
Rights = case when Read = 1 then 'R' else '' end
+ '/'
+ case when Add = 1 then 'A' else '' end
+ '/'
+ case when Edit = 1 then 'E' else '' end
+ '/'
+ case when Delete = 1 then 'D' else '' end
from table
[/code]

KH
[spoiler]Time is always against us[/spoiler]

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2009-07-22 : 02:10:11

Hi Khtan,

It is perfectly satisfied my requirement. Thanks for your help.

Regards,
Kamal.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-22 : 02:12:41

[code]
select AccessId, Rights = left(Rights, len(Rights) - 1)
from
(
select AccessId,
Rights = case when nullif([Read], 0) is not null then 'R/' else '' end
+ case when nullif([Add], 0) is not null then 'A/' else '' end
+ case when nullif([Edit], 0) is not null then 'E/' else '' end
+ case when nullif([Delete], 0) is not null then 'D/' else '' end
from table
) r
[/code]

KH
[spoiler]Time is always against us[/spoiler]

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-22 : 02:36:39

If any AccessID has all zeros, need to pad it, else the left() function will fail.


select	AccessId, Rights = Replace(left(Rights, len(Rights) - 1), '*', '')
from
(
	select	AccessId,
		Rights	= '*' +
			case when nullif([Read], 0)   is not null then 'R/' else '' end
			+ case when nullif([Add], 0)    is not null then 'A/' else '' end
			+ case when nullif([Edit], 0)   is not null then 'E/' else '' end
			+ case when nullif([Delete], 0) is not null then 'D/' else '' end
	from	table
) r

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-22 : 02:38:38

good catch

KH
[spoiler]Time is always against us[/spoiler]

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2009-07-22 : 02:43:28

Really good stuff.

thanks.

kamal.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources