Query Results as a Simple List?

[NontechnicalAdam]

As you might guess from my username, I am nontechnical--but I am learning quickly.

I have a junction table with UserID related to ObjectID according to RoleID.

I want to be able to query the ObjectID and Role ID and have it return a string such as, "RoleID: [User1], [User2]"

I apologize if this is answered elsewhere. I have looked fairly extensively, but I do not know the correct verbiage to search for such a solution.

All help is appreciated.

Cheers!

[Wikiwi]

Is this for display purposes in your DataPage or is this query going to be saved on some table? If it's the later one, I would suggest creating a task that will filter your table based on your "query" and then create a loop to generate your desired format: "RoleID: [User1], [User2]"

[NontechnicalAdam]

Thanks for taking a look at this! This is for displaying in the data page. 

[cheonsa]

Hello @NontechnicalAdam,

You can add a Calculated Field in the Report and use this formula:

'Role ID:' + (SELECT ABC = STUFF((SELECT ', ' + CAST(USER AS nvarchar) FROM TABLENAME WHERE ID = target.[@field:ID] FOR XML PATH ('')), 1, 1, ''))

 

Please note to change the highlighted words based on the correct field names and Table Name. 

[IamBlossom]

For more details about how XML Path works in SQL, you may check this link:

https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-serverhttps://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server