Subquery results in one field (column)

Question

Post reply

Subquery results in one field (column)

Bob Bridges-173967

SSCrazy

Points: 2094
More actions
March 18, 2008 at 3:35 pm

#185300

Hello gurus...
We are implementing an organization charting solution to be fed from our HR system (PeopleSoft). Today we were discussing the fields, etc that will be contained in the PS view that will feed the org charting software and they told me I need to do something that I have never heard of before. (Admittedly, I would never classify myself as a T-SQL expert, but then again, I have been around the block once or twice.)
To enforce row-level security, we need to populate a field they call an "access control list". For example if Employee number 123 is an HR employee and this person can see any employee with a location of London, Detroit or Dallas, her row in the view will contain her Employee number, name... and a field called ACL. This ACL should contain the locations (separated by commas) London, Detroit, Dallas (all in one field.)
Most of our PS data comes from a table called JOB. My simplified SQL would look something like
select EMPLID, name, dept, location, (select statement that returns the different locations that this employee has access to) from JOB
The results would look something like...
EMPLID NAME DEPT Location ACL
01234 Sue 2050 Dallas Dallas, Detroit, London
02547 Fred 5885 Erie Erie, London
Any ideas would be greatly appreciated.
Bob

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 1

Here's both an answer to your current dilema and what to watch out for in the process...

http://www.sqlservercentral.com/articles/Test+Data/61572/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bob Bridges-173967 SSCrazy Points: 2094 More actions · Answer 2

Thanks Jeff,

When I run this sql to create the data, I get the following errors...

Server: Msg 208, Level 16, State 1, Line 7

Invalid object name 'Master.dbo.SysColumns'.

Server: Msg 208, Level 16, State 1, Line 7

Invalid object name 'Master.dbo.SysColumns'.

Should I be doing this against the Master table or a user table? (I get the same results both ways.)

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 3

I take it you're talking about the following code?

SELECT TOP 10000 --<<<LOOK! CHANGE THIS NUMBER TO CHANGE THE NUMBER OF ROWS!

RowNum = IDENTITY(INT,1,1),

SomeID = ABS(CHECKSUM(NEWID()))%25+1, --<<<LOOK! CHANGE THIS NUMBER TO 1/400th THE ROW COUNT

SomeCode = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

INTO dbo.TestData

FROM Master.dbo.SysColumns t1,

Master.dbo.SysColumns t2

Just to make sure, I executed the code against both SQL Server 2000 and 2005... no problems.

Just so you know... that's an article that was written before they fixed the code windows... you should copy and paste to WORD and do a search/replace on ^l (circumflex-lowercase "L") with ^p (circumflex-lowercase "P") to restore the end of line markers... then, copy that to Query Analyzer or SSMS, whichever you are using (Probably QA since this is a 2k forum, but just making sure).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)