Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Fetching records optimally Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 10:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 5:50 AM
Points: 72, Visits: 225
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
Post #1433893
Posted Thursday, March 21, 2013 10:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:38 PM
Points: 13,081, Visits: 11,916
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1433900
Posted Thursday, March 21, 2013 11:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 5:50 AM
Points: 72, Visits: 225
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
Post #1433941
Posted Thursday, March 21, 2013 12:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:38 PM
Points: 13,081, Visits: 11,916
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1433968
Posted Friday, March 22, 2013 4:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 5:50 AM
Points: 72, Visits: 225
Thank you Sean.
Post #1434183
Posted Friday, March 22, 2013 7:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:38 PM
Points: 13,081, Visits: 11,916
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1434265
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse