Tricky Query - 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

General SQL Server Forums

New to SQL Server Programming

Tricky Query

Author

Topic

jstockho
Starting Member

2 Posts

Posted - 2010-05-08 : 00:50:04

I'm trying to get data from one DB to another and the interface I have to use requires me to write a query that generates a result set matching a specific format. I'm having trouble figuring out how I can do that using SQL only.

I have the following two tables:

AccessRightPersonMap
personID | AccessRightID
1 | 101
1 | 102
1 | 103
2 | 101
2 | 102

AccessRightDescription
ID | Description
101 | Can Use the System
102 | Can Receive Data
103 | Can Send Data

The result set I need to get looks like this
personID | canUseSystem | canReceive | canSend
1 | Y | Y | Y
2 | Y | Y | N

Any idea how I can achieve this with a query? I'm at a loss. I can't figure out how to select multiple Access Rights from the first table, all with the same personID, then combine them all into a single row, replacing each one with a 'Y' if it's there and an 'N' if it's not.

The interface I'm working with takes in a SQL statement, executes it expecting the above format for output, and then stores the results into its own DB. I can't rewrite the interface, unfortunately, so I have to do this all in an SQL statement.

Thanks for any help you can offer!

sathiesh2005
Yak Posting Veteran

85 Posts

Posted - 2010-05-08 : 02:47:59

using case statment you can do that.

Regards,
Sathieshkumar. R

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-08 : 06:08:39

[code]SELECT personID,
MAX(CASE WHEN Description ='Can Use the System' THEN 'Y' ELSE 'N' END) AS CanUseSystem,
MAX(CASE WHEN Description ='Can Receive Data' THEN 'Y' ELSE 'N' END) AS CanReceive,
MAX(CASE WHEN Description ='Can Send Data' THEN 'Y' ELSE 'N' END) AS CanSend
FROM AccessRightPersonMap pm
INNER JOIN AccessRightDescription d
ON d.ID = pm.AccessRightID
GROUP BY personID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jstockho
Starting Member

2 Posts

Posted - 2010-05-08 : 11:46:52

Oh, wow. That worked perfectly! I cannot tell you how long I tried to figure that out. I was stuck on how to get the case to work right. I didn't know how to use the MAX or GROUP_BY functions. Can you let me know if the below description is correct?

If I did this:
SELECT personID,
CASE WHEN AccessRightId=1 THEN 'Y' ELSE 'N' END AS CanUseSystem,
CASE WHEN AccessRightId=3 THEN 'Y' ELSE 'N' END AS CanReceive,
CASE WHEN AccessRightId=4 THEN 'Y' ELSE 'N' END AS CanSend
FROM PersonAccessRightMapTable

I get every row from the AccessRightDescription table in my results, so I need to group together into a single row, which is why you use GROUP_BY. Then, each other column needs to have a single value for the final row, so it must use an aggregate. You use MAX because 'Y' is greater than 'N', so if there's a Y in any of the initial rows returned, you'll favor that in your final results.

Is that right?

Again, thanks for your help. I got thrown into this project and my SQL was very light. The most complicated thing I've had to do is to join three tables together and query the results, but there was always a 1-to-1 relationship between the rows in all those tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources