March 21, 2013 at 10:14 am
CREATE TABLE tblTestId
(
id int,
NAME VARCHAR(10)
);
INSERT INTO tblTestId
SELECT 1,'Empa'
UNION ALL
SELECT 2,'Empa'
UNION ALL
SELECT 3,'Empa'
UNION ALL
SELECT 4,'Empa'
UNION ALL
SELECT 5,'Empa'
UNION ALL
SELECT 1,'Empb'
UNION ALL
SELECT 2,'Empb';
UNION ALL
SELECT 4,'Empc';
I have data as above in the table.
I have Ids range 1-4 and Name can have any of following rule.
i have business rule ,
If id in (1,2,3) it is R
If id = 4 it is C
If id in (1,2,3,4) it is RC
Now I want to fetch record with group by Name, and it should display record as per above rule.
e.g.
Name status
Empa RC
Empb R
Empc C
March 21, 2013 at 10:30 am
What have you tried so far?
_______________________________________________________________
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/
March 21, 2013 at 11:45 am
tried case
SELECT NAME ,
CASE WHEN id = 4
AND id NOT IN ( 1, 2, 3 ) THEN 'C'
WHEN id <> 4
AND id IN ( 1, 2, 3 ) THEN 'R'
WHEN id IN ( 1, 2, 3, 4 ) THEN 'RC'
ELSE CONVERT (VARCHAR(10), id)
END
FROM tblTestId
March 21, 2013 at 12:40 pm
This should work. I think you were pretty close but the RC one is a little bit more difficult.
select Name,
case
when Max(id) < 4 then 'R'
when MAX(id) = 4 and min(id) = 4 then 'C'
when min(id) < 4 and exists(select * from tblTestId t2 where id = 4 and t2.Name = t.Name) then 'RC'
end as [Status]
from tblTestId t
group by Name
order by Name
_______________________________________________________________
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/
March 22, 2013 at 4:22 am
Thank you Sean.
March 22, 2013 at 7:29 am
You are welcome. Thanks for letting me know that worked.
_______________________________________________________________
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy