-1

I have this query:

SELECT U.UserName, C.Name,
FROM CompanyUserMapping CU 
INNER JOIN Users U ON U.ID = CU.USERID 
INNER JOIN Company C ON C.ID = CU.CompanyID

This returns a result of which user has access to which company:

UserNAme     CompanyName
------------------------
userOne      CompanyOne  
UserOne      CompanyTwo  
UserTwo      CompanyOne

I want to modify the query so that I get :

Username     CompanyName1   CompanyName2
----------------------------------------
UserOne      CompanyOne     CompanyTwo
UserTwo      CompanyOne     NULL

Also, something like: 

UserNAme     CompanyName
-------------------------------------
userOne      CompanyOne, CompanyTwo  
UserTwo      CompanyOne

Any help is appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SurajS
  • 473
  • 7
  • 20
  • If you know the number of columns you can use conditional aggregation. Otherwise you will need either a dynamic crosstab or a dynamic pivot. This has been answered dozens and dozens of times. – Sean Lange Aug 29 '18 at 20:11
  • https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv – Paul Abbott Aug 29 '18 at 20:14
  • Yeah which one of the two outputs do you really want. They are very different from each other. – Sean Lange Aug 29 '18 at 20:15
  • @SeanLange both of them, second one is easy I guess, and that's my workaround if first one is too difficult. – SurajS Aug 29 '18 at 20:17
  • In the first one, you may have 'n' number of the columns for the company name.. based on the matched company name per user... – Mittal Patel Aug 29 '18 at 20:20
  • @PaulAbbott, thank you for link, but that solves the 2nd problem. First is still primary – SurajS Aug 29 '18 at 20:25
  • See my comment then search this site. It has been solved over and over and over. – Sean Lange Aug 29 '18 at 20:41

2 Answers2

1

You can achieve second result it as below:

SELECT UI.UserName, Name = 
    STUFF((SELECT ', ' + C.Name
           FROM CompanyUserMapping CU
           INNER JOIN Users U ON U.ID = CU.USERID 
           INNER JOIN Company C on  C.ID = CU.CompanyID
           WHERE U.UserName = UI.UserName 
          FOR XML PATH('')), 1, 2, '')
FROM Users UI
GROUP BY UserName
Mittal Patel
  • 2,732
  • 14
  • 23
1

You can try this use dynamic SQL to make solution one.

make row number to be the pivot base condition (condition aggregate function) MAX with CASE WHEN, then use var @col to carry the SQL syntax.

then use EXEC execute the SQL dynamically. 

CREATE TABLE T(
   UserName VARCHAR(50),
   CompanyName VARCHAR(50)
);


INSERT INTO T VALUES ('UserOne','CompanyOne');  
INSERT INTO T VALUES ('UserOne','CompanyTwo');  
INSERT INTO T VALUES ('UserTwo','CompanyOne');  


DECLARE @tables AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX),
        @col AS NVARCHAR(MAX);

WITH CTE AS (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY UserName ORDER BY UserName) rn
    FROM T 
),CTE1 AS(
    SELECT MIN(rn) smallRN,MAX(rn) bigRN,UserName
    FROM CTE 
    group by UserName
    UNION ALL
    SELECT smallRN+1,bigRN,UserName
    FROM CTE1
    WHERE smallRN < bigRN
)
SELECT @col = STUFF((SELECT distinct ', MAX(CASE WHEN rn = '+CAST(smallRN AS VARCHAR(50)) +' then CompanyName end) as CompanyName' +CAST(smallRN AS VARCHAR(50)) 
            FROM CTE1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = '
WITH CTE AS (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY UserName ORDER BY UserName) rn
    FROM T 
)
SELECT UserName,'+@col+' 
FROM CTE t1 
GROUP BY UserName

'


EXEC (@query)

RESULT

UserName    CompanyName1    CompanyName2
UserOne     CompanyOne      CompanyTwo
UserTwo     CompanyOne

sqlfiddle

D-Shih
  • 44,943
  • 6
  • 31
  • 51