0

Using SQL Server 2017, I am running a fairly straightforward query of:

SELECT SecCode,Allowed FROM Security

And that is returning a fairly straightforward table as shown here:

SecCode Allowed
MNCUPR GroupA
SEC000 GroupB,GroupC
SEC001 GroupA,GroupC
SEC005 GroupD
SEC007 GroupA,GroupB,GroupD

I am trying to determine what options I have (if any) to perform a few additional tasks in this query:

  1. Split the results in 'Allowed' by comma, giving me separate entities/tokens for each Group in a given row.
  2. Run an additional query of: SELECT Users from Users WHERE GroupList LIKE %GROUP_FROM_MAIN_QUERY%

Hopefully returning something like:

SecCode Allowed
MNCUPR Adam,Alex
SEC000 Billy,Bobby,Charlie,Chuck,Charles
SEC001 Adam,Alex,Charlie,Chuck,Charles
SEC005 Dylan,David
SEC007 Adam,Alex,Billy,Bobby,Dylan,David

Would this best be handled by a stored procedure? Or some form of a cte?

I'm open to any suggestions, as I am using this as more of a learning experience than anything else.

Jason H.
  • 9
  • 2
  • 1
    You mean `STRING_SPLIT` and `STRING_AGG`...? Though the better question is "Why are you storing denormalised, comma delimited data, in your relational database in the first place?" – Thom A Jul 25 '22 at 14:31
  • 1
    @Larnu - If it was my database, I would be happy to give you an answer. This is from our ERP system, Epicor. – Jason H. Jul 25 '22 at 14:33
  • Well, like I mentioned, you can use `STRING_SPLIT` and `STRING_AGG`; there are *literally* **100**'s of duplicates out there on how to use these. Though, again, I strongly recommend fixing the design; if you can't do that, then feed back to whomever can. – Thom A Jul 25 '22 at 14:34
  • Your previous question involves using STRING_SPLIT() - seems like that can be applied here as well – SMor Jul 25 '22 at 14:34
  • While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky Jul 25 '22 at 14:58
  • "a stored procedure?" and "some form of a cte" are orthogonal to the actual question you are asking. You can do it with or without them, what you need are `STRING_SPLIT` and `STRING_AGG` – Charlieface Jul 25 '22 at 15:22

0 Answers0