none
Selecting the same field but filter twice into different fields

    Question

  • I have a quick question I think... I've got a field "User Name" I need to filter by another field in the row. But I have to return two values. I need the User where User Role - Assessing Official in one column and I need the User where User Role - Admin in the 2nd column.

    To get the first one I just do this...

    SELECT     TABLEA.[User Role], TABLEA.[User Name]

    FROM        TABLEA

    WHERE     (TABLEA.[User Role] = N'Assessing Official')

    But how would I change this to get both values in two different columns. I don't want to filter it once for all users who are Assessing Officials and Admins. I want Col1 to have Assessing Officials and Col2 to have Admins.

    Wednesday, June 19, 2013 7:29 PM

Answers

  • Hi,

    New data, new query :)

    Please try this, it worked to me:

    -- For demo, uses tempdb
    use tempdb;
    GO
    
    DROP TABLE TABLEA;
    
    -- Create demo table and insert dummy record:
    CREATE TABLE TABLEA ([Contract Number] VARCHAR(50), [User Name] VARCHAR(50), [User Role] VARCHAR(50));
    INSERT INTO TABLEA VALUES ('XX-XX-123', 'Sam', 'Admin'),
    	('XX-XX-123', 'Tom', 'Assessing Official'),
    	('XX-XX-456', 'Jim', 'Admin'),
    	('XX-XX-456', 'Steve', 'Assessing Official');
    
    SELECT DISTINCT [Contract Number],
    	(SELECT [User Name] FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Admin') AS [Admin], 
    	(SELECT [User Name] FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Assessing Official') AS [Assessing Official]  FROM TABLEA

    Hope this helps,


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Pedro Martins
    Portugal
    https://www.linkedin.com/in/rechousa

    • Marked as answer by DCDeez Thursday, June 20, 2013 6:46 PM
    Thursday, June 20, 2013 3:26 PM
  • Yes you can, but you must retrieve only one row (you can do it using TOP 1):

    -- For demo, uses tempdb
    use tempdb;
    GO
    
    DROP TABLE TABLEA;
    
    -- Create demo table and insert dummy record:
    CREATE TABLE TABLEA ([Contract Number] VARCHAR(50), [User Name] VARCHAR(50), [User Role] VARCHAR(50));
    INSERT INTO TABLEA VALUES ('XX-XX-123', 'Sam', 'Admin'),
    	('XX-XX-123', 'Tom', 'Assessing Official'),
    	('XX-XX-456', 'Jim', 'Admin'),
    	('XX-XX-456', 'Mike', 'Peer'),
    	('XX-XX-456', 'John', 'Peer'),
    	('XX-XX-456', 'Linda', 'Peer'),
    	('XX-XX-456', 'Charles', 'Peer'),
    	('XX-XX-456', 'Fred', 'Peer'),
    	('XX-XX-456', 'Steve', 'Assessing Official');
    
    SELECT DISTINCT [Contract Number],
    	(SELECT [User Name] FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Admin') AS [Admin], 
    	(SELECT [User Name] FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Assessing Official') AS [Assessing Official],
    	(SELECT TOP 1 [User Name] FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Peer') AS [Peer]
    	FROM TABLEA;

    Hope this helps,


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Pedro Martins
    Portugal
    https://www.linkedin.com/in/rechousa

    • Marked as answer by DCDeez Thursday, June 20, 2013 6:46 PM
    Thursday, June 20, 2013 4:39 PM
  • Where we go :)

    -- For demo, uses tempdb
    use tempdb;
    GO
    
    DROP TABLE TABLEA;
    
    -- Create demo table and insert dummy record:
    CREATE TABLE TABLEA ([Contract Number] VARCHAR(50), [User Name] VARCHAR(50), [User Role] VARCHAR(50));
    INSERT INTO TABLEA VALUES ('XX-XX-123', 'Sam', 'Admin'),
    	('XX-XX-123', 'Tom', 'Assessing Official'),
    	('XX-XX-456', 'Jim', 'Admin'),
    	('XX-XX-456', 'Mike', 'Peer'),
    	('XX-XX-456', 'John', 'Peer'),
    	('XX-XX-456', 'Linda', 'Peer'),
    	('XX-XX-456', 'Charles', 'Peer'),
    	('XX-XX-456', 'Fred', 'Peer'),
    	('XX-XX-456', 'Steve', 'Assessing Official');
    
    
    SELECT DISTINCT [Contract Number],
    	(SELECT [User Name] FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Admin') AS [Admin], 
    	(SELECT [User Name] FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Assessing Official') AS [Assessing Official],
    	REPLACE((SELECT [User Name] AS 'data()' FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Peer' FOR XML PATH('')), ' ', ', ') AS [Peer]
    	FROM TABLEA;

    This was the fist time I've used XML to handle this kind of data.

    I do this usually with COALESCE with a variable, but not inside a row.

    So, today, I've learned something new! Thank you for this challenge!

    Hope this helps,


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Pedro Martins
    Portugal
    https://www.linkedin.com/in/rechousa

    • Marked as answer by DCDeez Thursday, June 20, 2013 7:42 PM
    • Unmarked as answer by DCDeez Thursday, June 20, 2013 7:42 PM
    • Marked as answer by DCDeez Thursday, June 20, 2013 7:43 PM
    Thursday, June 20, 2013 7:00 PM

All replies

  • I don't understand the question. Can you give an example of the data you have in the table and what you want to get from the query?

    I guess you want to have join in your query but I need to see an example to answer your question.


    Tatyana Yakushev [PredixionSoftware.com]

    Download Predixion Insight 3.0 - World class predictive platform for big data

    Thursday, June 20, 2013 12:58 AM
    Answerer
  • Hi,

    Like this?

    -- For demo, uses tempdb
    use tempdb;
    GO
    
    -- Create demo table and insert dummy record:
    CREATE TABLE TABLEA ([User Role] VARCHAR(50), [User Name] VARCHAR(50));
    INSERT INTO TABLEA VALUES ('Assessing Official', 'Assessing Official user name'), ('User Role Demo1', 'user name 1'), ('Admin', 'admin user name'), ('User Role Demo2', 'user name 2'), ('User Role Demo3', 'user name 3');
    
    -- Your query:
    SELECT [User Role1], [User Name1], [User Role2], [User Name2]
    FROM
    (
    	SELECT TABLEA.[User Role] AS [User Role2], TABLEA.[User Name] AS [User Name2]
    		FROM TABLEA
    		WHERE (TABLEA.[User Role] = N'Admin')
    ) A,
    (
    	SELECT TABLEA.[User Role] AS [User Role1], TABLEA.[User Name] AS [User Name1]
    		FROM TABLEA
    		WHERE (TABLEA.[User Role] = N'Assessing Official')
    ) B

    Hope this helps,


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Pedro Martins
    Portugal
    https://www.linkedin.com/in/rechousa

    • Marked as answer by DCDeez Thursday, June 20, 2013 12:47 PM
    • Unmarked as answer by DCDeez Thursday, June 20, 2013 1:49 PM
    • Marked as answer by DCDeez Thursday, June 20, 2013 1:54 PM
    • Unmarked as answer by DCDeez Thursday, June 20, 2013 1:57 PM
    Thursday, June 20, 2013 1:21 AM
  • Rehcousa,

    This is close, But this returns like 1000x my results. I'm trying to figure out why.

    Thursday, June 20, 2013 1:59 PM
  • my original data has 800 rows with combinations like

    A    3

    B    2

    B    3

    A    1

    BUT my new table returns

    A   1

    A   1

    A   1

    A   2

    A   2

    A   2

    A   3

    B   1

    B   2

    B   3

    C   1

    C   2

    C   3

    And goes on and on.

    Thursday, June 20, 2013 2:14 PM
  • Hi,

    One question: Do you want to return the results when a certain user has two roles (Assessing Official and Admin) at the same time?

    You can shared some of data you have. can you share some of the data you need to receive?

    Thanks


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Pedro Martins
    Portugal
    https://www.linkedin.com/in/rechousa

    Thursday, June 20, 2013 2:52 PM
  • Sorry I'll try to clear it up better.

    This table is a list of users. each row has a contract number " XX-XX-123". There might be 5 rows with the same contract number and 5 different users with different roles. Is it possible to have my table go from something like...

    Contract Number    UserName     UserRole

    XX-XX-123             Sam              Admin

    XX-XX-123             Tom              Assessing Official

    XX-XX-456             Jim               Admin

    XX-XX-456             Steve            Assessing Official

    To something like this...

    Contract Number    Admin       Assessing Official

    XX-XX-123              Sam         Tom

    XX-XX-456              Jim           Steve

    There will never be more than one admin or one assessing official per contract number,



    • Edited by DCDeez Thursday, June 20, 2013 3:07 PM more info
    Thursday, June 20, 2013 3:05 PM
  • Hi,

    New data, new query :)

    Please try this, it worked to me:

    -- For demo, uses tempdb
    use tempdb;
    GO
    
    DROP TABLE TABLEA;
    
    -- Create demo table and insert dummy record:
    CREATE TABLE TABLEA ([Contract Number] VARCHAR(50), [User Name] VARCHAR(50), [User Role] VARCHAR(50));
    INSERT INTO TABLEA VALUES ('XX-XX-123', 'Sam', 'Admin'),
    	('XX-XX-123', 'Tom', 'Assessing Official'),
    	('XX-XX-456', 'Jim', 'Admin'),
    	('XX-XX-456', 'Steve', 'Assessing Official');
    
    SELECT DISTINCT [Contract Number],
    	(SELECT [User Name] FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Admin') AS [Admin], 
    	(SELECT [User Name] FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Assessing Official') AS [Assessing Official]  FROM TABLEA

    Hope this helps,


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Pedro Martins
    Portugal
    https://www.linkedin.com/in/rechousa

    • Marked as answer by DCDeez Thursday, June 20, 2013 6:46 PM
    Thursday, June 20, 2013 3:26 PM
  • Try this:

    DECLARE @temp table
    (ContractNumber varchar(15),
        UserName varchar(50),
    	UserRole varchar(50)
    )
    INSERT INTO @temp
    SELECT 'XX-XX-123','Sam','Admin'
    UNION ALL
    SELECT 'XX-XX-123','Tom','Assessing Official'
    UNION ALL
    SELECT 'XX-XX-456','Jim','Admin'
    UNION ALL
    SELECT 'XX-XX-456','Steve','Assessing Official'
    SELECT 
    	a.ContractNumber,
    	[Admin] = a.UserName,
    	[Assessing Official] = ao.UserName
    FROM @temp a
    	INNER JOIN @temp ao 
    	ON ao.ContractNumber = a.ContractNumber
    	and ao.UserRole = 'Assessing Official'
    WHERE a.UserRole IN ('Admin')

    Thursday, June 20, 2013 3:37 PM
  • You're a genius!

    What if I wanted to add another role. BUT the 3rd role might have more than one user assigned to it. Would that even be possible?

    Like...

    SELECT DISTINCT [Contract Number],
    (SELECT [User Name] FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Admin') AS [Admin],
    (SELECT [User Name] FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Assessing Official') AS [Assessing Official],

    (SELECT [User Name] FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Peer') AS [Peer],

      FROM TABLEA

    I know the above statement would error out when it hit the 2nd "peer" for a Contract Number.

    Thursday, June 20, 2013 4:28 PM
  • Yes you can, but you must retrieve only one row (you can do it using TOP 1):

    -- For demo, uses tempdb
    use tempdb;
    GO
    
    DROP TABLE TABLEA;
    
    -- Create demo table and insert dummy record:
    CREATE TABLE TABLEA ([Contract Number] VARCHAR(50), [User Name] VARCHAR(50), [User Role] VARCHAR(50));
    INSERT INTO TABLEA VALUES ('XX-XX-123', 'Sam', 'Admin'),
    	('XX-XX-123', 'Tom', 'Assessing Official'),
    	('XX-XX-456', 'Jim', 'Admin'),
    	('XX-XX-456', 'Mike', 'Peer'),
    	('XX-XX-456', 'John', 'Peer'),
    	('XX-XX-456', 'Linda', 'Peer'),
    	('XX-XX-456', 'Charles', 'Peer'),
    	('XX-XX-456', 'Fred', 'Peer'),
    	('XX-XX-456', 'Steve', 'Assessing Official');
    
    SELECT DISTINCT [Contract Number],
    	(SELECT [User Name] FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Admin') AS [Admin], 
    	(SELECT [User Name] FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Assessing Official') AS [Assessing Official],
    	(SELECT TOP 1 [User Name] FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Peer') AS [Peer]
    	FROM TABLEA;

    Hope this helps,


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Pedro Martins
    Portugal
    https://www.linkedin.com/in/rechousa

    • Marked as answer by DCDeez Thursday, June 20, 2013 6:46 PM
    Thursday, June 20, 2013 4:39 PM
  • I should probably start a new thread but I'll try one more variant.

    What would you do to display all the Peers in a contract?

    SELECT DISTINCT [Contract Number],

    (SELECT [User Name] FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Admin') AS [Admin],
    (SELECT [User Name] FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Assessing Official') AS [Assessing Official],
    (SELECT TOP 1 [User Name] FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Peer') AS [Peer]
    FROM TABLEA;

    And somehow return....

    Contract         Admin           Assessing Official          Peer

    xx-xx-456       Jim                Steve                       Mike, John, Linda, Charles, Fred

    Thursday, June 20, 2013 6:49 PM
  • Where we go :)

    -- For demo, uses tempdb
    use tempdb;
    GO
    
    DROP TABLE TABLEA;
    
    -- Create demo table and insert dummy record:
    CREATE TABLE TABLEA ([Contract Number] VARCHAR(50), [User Name] VARCHAR(50), [User Role] VARCHAR(50));
    INSERT INTO TABLEA VALUES ('XX-XX-123', 'Sam', 'Admin'),
    	('XX-XX-123', 'Tom', 'Assessing Official'),
    	('XX-XX-456', 'Jim', 'Admin'),
    	('XX-XX-456', 'Mike', 'Peer'),
    	('XX-XX-456', 'John', 'Peer'),
    	('XX-XX-456', 'Linda', 'Peer'),
    	('XX-XX-456', 'Charles', 'Peer'),
    	('XX-XX-456', 'Fred', 'Peer'),
    	('XX-XX-456', 'Steve', 'Assessing Official');
    
    
    SELECT DISTINCT [Contract Number],
    	(SELECT [User Name] FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Admin') AS [Admin], 
    	(SELECT [User Name] FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Assessing Official') AS [Assessing Official],
    	REPLACE((SELECT [User Name] AS 'data()' FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Peer' FOR XML PATH('')), ' ', ', ') AS [Peer]
    	FROM TABLEA;

    This was the fist time I've used XML to handle this kind of data.

    I do this usually with COALESCE with a variable, but not inside a row.

    So, today, I've learned something new! Thank you for this challenge!

    Hope this helps,


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Pedro Martins
    Portugal
    https://www.linkedin.com/in/rechousa

    • Marked as answer by DCDeez Thursday, June 20, 2013 7:42 PM
    • Unmarked as answer by DCDeez Thursday, June 20, 2013 7:42 PM
    • Marked as answer by DCDeez Thursday, June 20, 2013 7:43 PM
    Thursday, June 20, 2013 7:00 PM
  • I've noticed sometimes REPLACE statements can get messy when you have multiple ones. Do you think there will be a problem down the line if I were to add another REPLACE in there?
    Thursday, June 20, 2013 7:43 PM