SQL - Union All Users in One Table

问题:

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)

回答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');

回答2:

use case when

select max(case when UserName='John' then fieldname end) as john,
       max(case when UserName='Philip' then fieldname end) as Philip,
       max(case when UserName='Khan' then fieldname end) as Khan
        from table_name

EDIT : You need row_number() :

select max(case when username = 'John' then friendname end) as [John],
       max(case when username = 'Philip' then friendname end) as [Philip],
       max(case when username = 'Khan' then friendname end) as [Khan],
       . . . 
from (select t.*,
             row_number() over (partition by username order by friendname) as seq
      from table t
     ) t
group by seq;

回答3:

I tink you can use pivot https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017

-- Pivot table with one row and five columns  
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,   
[0], [1], [2], [3], [4]  
FROM  
(SELECT DaysToManufacture, StandardCost   
FROM Production.Product) AS SourceTable  
PIVOT  
(  
AVG(StandardCost)  
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])  
) AS PivotTable;  

that is the code:

if OBJECT_ID('userName') is not null drop table userName;

create table userName (fiiduserName int identity(1,1), fcName varchar(20), 
fcFrienlyName varchar(20));

insert into userName(fcName, fcFrienlyName)values ('John', 'Sarah');
insert into userName(fcName, fcFrienlyName)values ('Philip', 'Ursula');
insert into userName(fcName, fcFrienlyName)values ('John', 'Marry');
insert into userName(fcName, fcFrienlyName)values ('John', 'Jeremy');
insert into userName(fcName, fcFrienlyName)values ('Philip', 'Brock');
insert into userName(fcName, fcFrienlyName)values ('Khan', 'Lemy');

declare @Nombres varchar(max);
declare @select varchar(max);

select @Nombres = COALESCE(@Nombres + ',', '') + '[' + fcName + ']'
from userName
group by fcName;

select @select  = 'SELECT fiiduserName, ' +  @Nombres + '
FROM  
(SELECT fiiduserName, fcName, fcFrienlyName
    FROM userName) AS SourceTable  
PIVOT  
(  
MIN(fcFrienlyName)  
FOR fcName IN (' + @Nombres + ')
) AS PivotTable; ' 

exec (@select);

drop table userName;