2

I am trying to write a SQL script in SSMS 2008 and have been looking around online for an answer and nothing I'm finding has been a complete solution.

I have information across several tables about Roles, Groups, and Profiles that are assigned to a user and a script to get that information. The results I'm getting are "Correct" but not being displayed in the way the customer would like to see them. The Roles, Groups, and Profiles are not related to each other in any way and there may be a different number of each of them assigned to one user (ie 2 roles, 1 group, and 4 profiles).

Here is my SQL script:

select distinct
DWUser.uid as ID,
DWUser.name as Name, 
DWRoles.name as Roles, 
DWGroup.name as Groups, 
DWFCProfile.name as Profiles
from DWUser
/* Joins for getting role name */
inner join DWUserToRole on DWUser.uid = DWUserToRole.uid
inner join DWRoles on DWUserToRole.rid = dwRoles.rid
/* Joins for getting group name */
inner join DWUserToGroup on DWUser.uid = DWUserToGroup.uid
inner join DWGroup on DWUserToGroup.gid = DWGroup.gid
/* Joins for getting profile name */
inner join DWFCProfileToUser on DWUser.uid = DWFCProfileToUser.uid
inner join DWFCProfile on DWFCProfileToUser.fpid = DWFCProfile.fpid

Which returns this result:

ID  Name        Roles                       Groups      Profiles

1   admin       Organization Administrator  Public      Contracts
1   admin       Organization Administrator  Public      My Basket 1
1   admin       Organization Administrator  Public      My Basket 2
1   admin       Organization Administrator  Public      My Basket 3
1   admin       System Administrator        Public      Contracts
1   admin       System Administrator        Public      My Basket 1
1   admin       System Administrator        Public      My Basket 2
1   admin       System Administrator        Public      My Basket 3
4   docuware    Organization Administrator  Public      Contracts
4   docuware    Organization Administrator  Public      My Basket 1
4   docuware    Organization Administrator  Public      My Basket 2
4   docuware    Organization Administrator  Public      My Basket 3
4   docuware    Organization Administrator  votosign    Contracts
4   docuware    Organization Administrator  votosign    My Basket 1
4   docuware    Organization Administrator  votosign    My Basket 2
4   docuware    Organization Administrator  votosign    My Basket 3

The way the customer wants to see the results is:

ID  Name        Roles                       Groups      Profiles

1   admin       Organization Administrator  Public      Contracts
                System Administrator                    My Basket 1
                                                        My Basket 2
                                                        My Basket 3
4   docuware    Organization Administrator  Public      Contracts
                                            votosign    My Basket 1
                                                        My Basket 2
                                                        My Basket 3

So basically for each user I need to remove any duplicate Roles, Groups, and Profiles regardless of what order they appear in the column (ie even if duplicates aren't "stacked" like they are in the Roles and Groups columns).

I'm pretty sure the answer is 'No', but here it goes:

Is there any way to output the desired result using Microsoft SQL?

Improve this question
1
  • 4
    This is a job for the presentation layer. Commented May 4, 2017 at 20:08

2 Answers 2

Reset to default
2

Here we concatinate strings via XML and STUFF() and then finally inject a CRLF as the delimiter

To be clear, in this case only two rows of data will be returned, but each could have multiple lines.

Example

Declare @YourTable Table ([ID] varchar(50),[Name] varchar(50),[Roles] varchar(50),[Groups] varchar(50),[Profiles] varchar(50))
Insert Into @YourTable Values
 (1,'admin','Organization Administrator','Public','Contracts')
,(1,'admin','Organization Administrator','Public','My Basket 1')
,(1,'admin','Organization Administrator','Public','My Basket 2')
,(1,'admin','Organization Administrator','Public','My Basket 3')
,(1,'admin','System Administrator','Public','Contracts')
,(1,'admin','System Administrator','Public','My Basket 1')
,(1,'admin','System Administrator','Public','My Basket 2')
,(1,'admin','System Administrator','Public','My Basket 3')
,(4,'docuware','Organization Administrator','Public','Contracts')
,(4,'docuware','Organization Administrator','Public','My Basket 1')
,(4,'docuware','Organization Administrator','Public','My Basket 2')
,(4,'docuware','Organization Administrator','Public','My Basket 3')
,(4,'docuware','Organization Administrator','votosign','Contracts')
,(4,'docuware','Organization Administrator','votosign','My Basket 1')
,(4,'docuware','Organization Administrator','votosign','My Basket 2')
,(4,'docuware','Organization Administrator','votosign','My Basket 3')

Select ID 
      ,Name 
      ,Roles    = replace((Select Stuff((Select Distinct '|'+Roles    From @YourTable Where ID=A.ID For XML Path ('')),1,1,'') ),'|',char(13)+char(10))
      ,Groups   = replace((Select Stuff((Select Distinct '|'+Groups   From @YourTable Where ID=A.ID For XML Path ('')),1,1,'') ),'|',char(13)+char(10))
      ,Profiles = replace((Select Stuff((Select Distinct '|'+Profiles From @YourTable Where ID=A.ID For XML Path ('')),1,1,'') ),'|',char(13)+char(10))
 From (
        Select Distinct ID,Name From @YourTable
      ) A

Returns

ID  Name     Roles                       Groups     Profiles
1   admin    Organization Administrator  Public     Contracts
             System Administrator                   My Basket 1
                                                    My Basket 2
                                                    My Basket 3
4   docuware Organization Administrator  Public     Contracts
                                         votosign   My Basket 1
                                                    My Basket 2
                                                    My Basket 3
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks! This is exactly the type of thing I was looking for.
@brandonstrong Happy it helped
1

It is primarily in UI/Presentation layer, but you can achieve this by using row_number and if it is 1 you can select else you can select NULL as below:

select ID = case when( Row_Number() over (partition by id order by id) = 1) then id else null end
    , Name = case when( row_number() over (partition by id order by id) = 1) then Name else null end
    , Roles = case when (row_number() over (partition by id, roles order by id) = 1) then Roles else null end
    , Groups = case when (row_number() over (partition by id, Groups order by id) = 1) then Groups else null end
    , Profiles
     from #yourreturnstable
Improve this answer

3 Comments

I tried replacing 'id' with the column name ie. Name = case when( row_number() over (partition by DWUser.uid order by DWUser.uid) = 1) then DWRoles.name else null end and the output doesn't have all the correct values and is not in the correct order. The user names are listed on the bottom left and the roles, groups, and profiles are scattered throughout the table in no coherent order. Did I substitute 'id' with the wrong values?
use id, Name for name partition by, id, name, roles for roles partition by and ,id, name, roles, groups in groups partition by ... Looks like your data is different than the sample data shown
Oh! Sorry, I read your answer incorrectly. Ok, so I tried it again and it mostly works but it only nullifies the duplicate values that are 'stacked' on one another. So for example it doesn't nullify any of the duplicate values in the 'Profiles' column for the 'admin' user

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.