Home MS SQL combine selects into one, char separator string
Reply: 0

MS SQL combine selects into one, char separator string

user1865
1#
user1865 Published in June 23, 2018, 9:17 pm

Hey all this is my query here:

SELECT 
    uT.employeeID + '|' + uT.lastFirstMiddle + '|' + uT.ntName + '|' + 
    uT.email + '|' + uT.firstName + '|' + uT.lastName + '|' + uT.active, 
    (
        SELECT 
            ',' + uP.type
        FROM 
            usersPermissions AS uP
        WHERE 
            ',' + uT.userPerms + ',' 
        LIKE 
            '%,' + cast(uP.id AS nvarchar(20)) + ',%'
        FOR 
            XML PATH(''), TYPE
    ).value('substring(text()[1], 2)', 'varchar(max)') AS userPerms,
    (
        SELECT 
            ',' + uP.name
        FROM 
            pagePermissions AS uP
        WHERE 
            ',' + uT.userPerms + ',' 
        LIKE 
            '%,' + cast(uP.id AS nvarchar(20)) + ',%'
        FOR 
            XML PATH(''), TYPE
    ).value('substring(text()[1], 2)', 'varchar(max)') AS userPermPages
FROM 
    usersTbl as uT 
WHERE 
    uT.id = '1';

And what I am looking to do is combine all those into just one output.

So instead of this:

(No Column Name)                                                       |userPerms         |userPermsPages
-----------------------------------------------------------------------+------------------+-----------------------------
R98T92C48|Barker, Bob|bbarker|bbarker@priceisright.com|Bob|Barker|true |Read,Upload,Admin |ANP Index,BOM import,BOM line

I would like something more along the lines of this:

(No Column Name)
-----------------------------------------------------------------------------------------------------------------------
R98T92C48|Barker, Bob|bbarker|bbarker@priceisright.com|Bob|Barker|true|Read|Upload|Admin|ANP Index|BOM import|BOM line

So how would the query look in order to output it like the above example?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.317035 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO