SQL - Union All Users in One Table

Question

Table : Popular

UserName   FriendName
--------   ---------- 
John       Sarah
Philip     Ursula
John       Marry
John       Jeremy
Philip     Brock
Khan       Lemy

And I want list with query;

John       Philip       Khan
--------   ----------   --------
Sarah      Ursula       Lemy
Marry      Brock        -NULL-
Jeremy     -NULL-       -NULL-

I have 100+ Username... help me for to list with SQL Query (MSSQL)

Answer 1

If you have "100+ UserNames" you will want this to be DYNAMIC so that you don't have to type out specific CASE statements for each UserName.

Also you won't want to have to update your script every time a new UserName is added to your table.

The below script will dynamically retrieve all distinct UserNames and create a column for them with rows for all their friends.

    DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);

    SET @cols = STUFF((SELECT distinct ',MAX(CASE WHEN UserName = ''' 
                        + p.UserName + ''' THEN FriendName END) AS ' 
                        + QUOTENAME(p.UserName) FROM Popular p
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

    SET @query = 'SELECT ' + @cols + ' FROM 
                (SELECT UserName, FriendName
                    ,ROW_NUMBER() OVER (PARTITION BY UserName ORDER BY FriendName) AS RowNum
                    FROM Popular GROUP BY USERNAME, FRIENDNAME
                ) x
                GROUP BY RowNum'

    EXECUTE(@query);

My output from the above shows as the below;

╔════════╦══════╦════════╗
║  John  ║ Khan ║ Philip ║
╠════════╬══════╬════════╣
║ Jeremy ║ Lemy ║ Brock  ║
║ Marry  ║ NULL ║ Ursula ║
║ Sarah  ║ NULL ║ NULL   ║
╚════════╩══════╩════════╝

You should be able to run this against entire table and get results for all possible UserNames without having to type out individual CASE Statements.

For anyone wanting to test this, here is the test table and data script;

    IF EXISTS ( SELECT *
                FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_NAME = 'Popular'
                AND TABLE_SCHEMA = 'dbo'
                AND TABLE_TYPE = 'TABLE')
    DROP TABLE [dbo].[Popular];
    GO

    CREATE TABLE [dbo].[Popular]
    (
    UserName VARCHAR(20),
    FriendName VARCHAR(20)
    );
    GO

    INSERT INTO [dbo].[Popular] (UserName,FriendName) VALUES
    ('John','Sarah'),
    ('Philip','Ursula'),
    ('John','Marry'),
    ('John','Jeremy'),
    ('Philip','Brock'),
    ('Khan','Lemy');