none
Working with two REPLACE statements in a SQL Query

    Question

  • I'm starting a new thread as my questions are starting to branch off.

    I've got a table with multiple fields. [Contract Number] is the same between some rows, But then they have Users with different roles. I need to lump the rows together from this table "TABLEA"...

    Contract Number   Peer    Admin

    xx-xx-123             Jon

    xx-xx-123             Mike    

    xx-xx-123                       Steve        

    yy-yy-234             Matt

    yy-yy-234                        Phil

    yy-yy-234                        Jay

    Into something like this...

    Contract Number   Peer             Admin

    xx-xx-123             Jon, Mike       Steve   

    yy-yy-234             Matt              Phil, Jay

    This statement below works...

    SELECT DISTINCT [Contract Number],
     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;

    But if I use this statement it doesn't like the 2nd REPLACE statement...

    SELECT DISTINCT [Contract Number],
     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]

     REPLACE((SELECT [User Name] AS 'data()' FROM TABLEA T WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Admin' FOR XML PATH('')), ' ', ', ') AS [Admin]
    FROM TABLEA;

    What am I doing wrong?

    Thursday, June 20, 2013 8:18 PM

Answers

  • Hello,

    Where do you see a relation between your question and "Data Mining", the subject of this forum? You should post your question to a more related forum.

    Which error message do you get? May "Incorrect syntax near REPLACE"? It's because you forgot to separate the 2 result columns with a comma.

    SELECT DISTINCT [Contract Number],
      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]
    
     ,REPLACE((SELECT [User Name] AS 'data()' 
               FROM TABLEA T 
    		   WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Admin' FOR XML PATH('')), ' ', ', ') AS [Admin]
    FROM TABLEA;


    Olaf Helper

    Blog Xing

    • Marked as answer by DCDeez Friday, June 21, 2013 4:57 PM
    Friday, June 21, 2013 4:26 AM

All replies

  • Hello,

    Where do you see a relation between your question and "Data Mining", the subject of this forum? You should post your question to a more related forum.

    Which error message do you get? May "Incorrect syntax near REPLACE"? It's because you forgot to separate the 2 result columns with a comma.

    SELECT DISTINCT [Contract Number],
      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]
    
     ,REPLACE((SELECT [User Name] AS 'data()' 
               FROM TABLEA T 
    		   WHERE T.[Contract Number] = TABLEA.[Contract Number] AND T.[User Role]='Admin' FOR XML PATH('')), ' ', ', ') AS [Admin]
    FROM TABLEA;


    Olaf Helper

    Blog Xing

    • Marked as answer by DCDeez Friday, June 21, 2013 4:57 PM
    Friday, June 21, 2013 4:26 AM
  • Because Google told me Data Mining is "... statistical algorithms to discover patterns and correlations in large preexisting databases; a way to discover new meaning in data."

    This could fall under that.

    But If Olaf Helper is so smart I challenge him to tell me why there is a "," appearing between spaces in the middle of the User name field now? For example "John Jones" when combined into the one row with "Mike Schmitt" yields "John, Jones, Mike, Schmitt".

    I figured the statement would say "John Jones, Mike Schmitt". 

    Friday, June 21, 2013 1:22 PM
  • This has really nothing to do with "Data Mining", that's something completely different.

    By your other post http://social.msdn.microsoft.com/Forums/en-US/e2eecbe6-3772-4aa9-a790-6b8836d656e8/when-using-replace-im-getting-an-extra-comma I think, this is solved?


    Olaf Helper

    Blog Xing


    • Edited by Olaf HelperMVP Friday, June 21, 2013 4:12 PM Additional comment
    Friday, June 21, 2013 4:07 PM
  • Yep, Thanks for your help on the first part!
    Friday, June 21, 2013 4:57 PM