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