Please see this select column and help me out.if the value is in 1,6 it should not display
values for 1 and 6...it should display only for 1and 6.
case
when (rtrim(keyvalue) in ('1')) then 'Sorted'
when (rtrim(keyvalue) in ('2')) then 'Multiple'
when (rtrim(keyvalue) in ('5')) then 'Deleted/Worked'
when (rtrim(keyvalue) in ('6')) then 'Incorrect'
when (rtrim(keyvalue) in ('7')) then ' Change'
when (rtrim(keyvalue) in ('1','6')) then 'Team'
when (rtrim(keyvalue) in ('1','7')) then 'Date'
when (rtrim(keyvalue) in ('7','6')) then 'Type'
when (rtrim(keyvalue) in ('1','7','6')) then 'Sort Type'
when (rtrim(keyvalue) in ('9')) then 're-controlled'
else rtrim(keyvalue)
end
presently if the keyvalue is in 1,6,7 the result is coming with three rows for 1,6,7....i want only with one row for1,6,7 value not individual values...
please help me out.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
The one column should only have one value, so :
keyvalue = '1' and keyvalue = '6'
This can't work.
keyvalue in ('1', '6')
Is same as :
keyvalue = '1' or keyvalue = '6'
Which is why you get multiple rows. Maybe you have a value like '1,6,7' and so you really want this :
keyvalue = '1,6,7'
You may want to show the actual data and your desired results.
M-1
You may be able to use GROUP_CONCAT() to get ordered list of the values.
Say x, y, z are in column called id then can look like this :
select id, group_concat(distinct keyvalue order by keyvalue) as keyvalues
from your_table_name
group by id ;
Then you can use the above as a derived table:
select id
, case keyvalues
when '1,6,7' then 'Sort Type'
when '1' then 'Sorted'
else '...' end
from ({query above}) derived
m-1
Thanks much..but still not working and the Error got is 'GROUP_CONCAT' is not a recognized function name (Iam using only SQL2000).
and more...
Can I use,
select id
, case keyvalues
when '1,6,7' then 'Sort Type'
when '1' then 'Sorted'
else '...' end,
t3.name,
ot.city,
from ({query above}) derived,Other table ot
let out join table3 t3 on ..............ot.p=t3.p
..........................
..........................
where
some conditions.
please let me know for this conditions.
Do you have a finite number of keyvalue values?
In other words is it always 1 - 10?
If so, then you may be able to "pivot" the data so that you end up with an id with 10 columns that are bits or single chars like 'Y' or 'N' that indicate the existence of keyvalues 1 - 10 that you can then use in conditional statements.
Alternatively, you can try this XML trick.
select id
, replace((
select keyvalue as "data()"
from keyvalues b
where a.id = b.id
for xml path('')
), ' ', ',') as keyvalues
from keyvalues a
group by id
Let me know.
>> SQL 2000 supports for xml just not the xml data type and some other features of for xml that are not being used here.
I am not sure about this.
mwvisa1 - Can you provide some URLs to support your statement.
hp746,
Create a function like this:
create function fn_CommaSeperatedString(@K
returns nvarchar(100) as
begin
DECLARE @str nvarchar(500)
SELECT @str = ''
SELECT @str = Value + ', ' + @str FROM YourTable where KeyValue = @KeyValue order by Value
SELECT LEFT(@str,LEN(@str)-1)
end
O
select KeyValue,
case
when (rtrim(Value) in ('1') then 'Sorted'
when (rtrim(Value) in ('2') then 'Multiple'
when (rtrim(Value) in ('5') then 'Deleted/Worked'
when (rtrim(Value) in ('6') then 'Incorrect'
when (rtrim(Value) in ('7') then ' Change'
when (rtrim(Value) in ('1,6') then 'Team'
when (rtrim(Value) in ('1,7') then 'Date'
when (rtrim(Value) in ('6,7') then 'Type'
when (rtrim(Value) in ('1,6,7') then 'Sort Type'
when (rtrim(Value) in ('9') then 're-controlled'
else rtrim(keyvalue)
end
from (select KeyValue,dbo.fn_CommaSeper
from YourTable
group by KeyValue) as t1
http://technet.microsoft.c
But more importantly, I ran the query successfully on SQL Server 2000, so simply try it ! :)
mwvisa and Sharath,
Thank you very much for your answers...
Do we have any simple way of doing this..with out using functions and all,because the tool the query should run doesn't have much like this.......Just a select,from and Where only I can use.Please let me know,If this can't be done simple,I will close this Q and give you points
Business Accounts
Answer for Membership
by: mattibuttPosted on 2009-11-07 at 11:06:55ID: 25767602
do group by keyvalue