Selecting the same column from Different rows Based on Different Criteria Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)SQL unpivoting multiple rows/columns, but keeping the rows grouped together, and in the same order they were selectedPage Split TimingMoving data from table with VARCHAR(50) fields to table with numeric fields increases table sizeRe-order columns values based on values in a tableHandling duplicate values using triggerWhy am I getting “Snapshot isolation transaction aborted due to update conflict”?Query runs slowly when a non-indexed column is added to the WHERE clauseProper table design for sparse primary keyMerging two rows into one by creating two new columns based on criteria from same table (SQL)Enforcing distinct number of trailing spaces

How to answer "Have you ever been terminated?"

Are two submodules (where one is contained in the other) isomorphic if their quotientmodules are isomorphic?

Apollo command module space walk?

What's the meaning of 間時肆拾貳 at a car parking sign

How can I (re)show post-installation notes?

Align equal signs while including text over equalities

Why is my conclusion inconsistent with the van't Hoff equation?

What does an IRS interview request entail when called in to verify expenses for a sole proprietor small business?

Should I discuss the type of campaign with my players?

Echoing a tail command produces unexpected output?

Using audio cues to encourage good posture

Why did the IBM 650 use bi-quinary?

How to react to hostile behavior from a senior developer?

Can a USB port passively 'listen only'?

Is there a (better) way to access $wpdb results?

Extract all GPU name, model and GPU ram

Single word antonym of "flightless"

What does the "x" in "x86" represent?

How would the world control an invulnerable immortal mass murderer?

How does the particle を relate to the verb 行く in the structure「A を + B に行く」?

ListPlot join points by nearest neighbor rather than order

What does the word "veer" mean here?

What are the pros and cons of Aerospike nosecones?

What would be the ideal power source for a cybernetic eye?



Selecting the same column from Different rows Based on Different Criteria



Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)SQL unpivoting multiple rows/columns, but keeping the rows grouped together, and in the same order they were selectedPage Split TimingMoving data from table with VARCHAR(50) fields to table with numeric fields increases table sizeRe-order columns values based on values in a tableHandling duplicate values using triggerWhy am I getting “Snapshot isolation transaction aborted due to update conflict”?Query runs slowly when a non-indexed column is added to the WHERE clauseProper table design for sparse primary keyMerging two rows into one by creating two new columns based on criteria from same table (SQL)Enforcing distinct number of trailing spaces



.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;








2















My table creates a row for each customer name for the unique customer number.



CREATE TABLE #src(Number int, name varchar(32), seq bit);

INSERT #src(Number,name,seq) VALUES
(12345,'Mickey Mouse',0),
(12345,'Minnie Mouse',1),
(45678,'Donald Duck',0),
(45678,'Daphney Duck',1),
(245678,'Pluto Dog',0);


I need to be able to return a single row with multiple name columns based on the "Seq" number. It will always either be a 0 or a 1 and the Seq 1 can sometimes be blank.



enter image description here










share|improve this question









New contributor




Nicole Montez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.


























    2















    My table creates a row for each customer name for the unique customer number.



    CREATE TABLE #src(Number int, name varchar(32), seq bit);

    INSERT #src(Number,name,seq) VALUES
    (12345,'Mickey Mouse',0),
    (12345,'Minnie Mouse',1),
    (45678,'Donald Duck',0),
    (45678,'Daphney Duck',1),
    (245678,'Pluto Dog',0);


    I need to be able to return a single row with multiple name columns based on the "Seq" number. It will always either be a 0 or a 1 and the Seq 1 can sometimes be blank.



    enter image description here










    share|improve this question









    New contributor




    Nicole Montez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.






















      2












      2








      2








      My table creates a row for each customer name for the unique customer number.



      CREATE TABLE #src(Number int, name varchar(32), seq bit);

      INSERT #src(Number,name,seq) VALUES
      (12345,'Mickey Mouse',0),
      (12345,'Minnie Mouse',1),
      (45678,'Donald Duck',0),
      (45678,'Daphney Duck',1),
      (245678,'Pluto Dog',0);


      I need to be able to return a single row with multiple name columns based on the "Seq" number. It will always either be a 0 or a 1 and the Seq 1 can sometimes be blank.



      enter image description here










      share|improve this question









      New contributor




      Nicole Montez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.












      My table creates a row for each customer name for the unique customer number.



      CREATE TABLE #src(Number int, name varchar(32), seq bit);

      INSERT #src(Number,name,seq) VALUES
      (12345,'Mickey Mouse',0),
      (12345,'Minnie Mouse',1),
      (45678,'Donald Duck',0),
      (45678,'Daphney Duck',1),
      (245678,'Pluto Dog',0);


      I need to be able to return a single row with multiple name columns based on the "Seq" number. It will always either be a 0 or a 1 and the Seq 1 can sometimes be blank.



      enter image description here







      sql-server sql-server-2012 pivot






      share|improve this question









      New contributor




      Nicole Montez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.











      share|improve this question









      New contributor




      Nicole Montez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      share|improve this question




      share|improve this question








      edited 2 hours ago









      Aaron Bertrand

      154k18299495




      154k18299495






      New contributor




      Nicole Montez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      asked 2 hours ago









      Nicole MontezNicole Montez

      111




      111




      New contributor




      Nicole Montez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.





      New contributor





      Nicole Montez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






      Nicole Montez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.




















          2 Answers
          2






          active

          oldest

          votes


















          2














          Given this table and data:



          CREATE TABLE #src(Number int, name varchar(32), seq bit);

          INSERT #src(Number,name,seq) VALUES
          (12345,'Mickey Mouse',0),
          (12345,'Minnie Mouse',1),
          (45678,'Donald Duck',0),
          (45678,'Daphney Duck',1),
          (245678,'Pluto Dog',0);


          You can apply a simple PIVOT:



          SELECT Number, Owner1 = [0], Owner2 = COALESCE([1],'')
          FROM #src AS c
          PIVOT (MAX(name) FOR seq IN ([0],[1])) AS p
          ORDER BY Number;


          Results:



          number Owner1 Owner2
          ------ ------------ ------------
          12345 Mickey Mouse Minnie Mouse
          45678 Donald Duck Daphney Duck
          245678 Pluto Dog


          I would just ensure that Number, seq is enforced to be unique and that seq is either a bit or has a constraint so that it can only be 0 or 1.






          share|improve this answer
































            1














            Another method without using pivot



            CREATE TABLE dbo.Customer(Number int, [Name] varchar(255),Seq bit);
            INSERT INTO dbo.Customer(Number,[Name],Seq)
            VALUES
            (12345,'Mickey Mouse',0),
            (12345,'Minnie Mouse',1),
            (45678,'Donald Duck',0),
            (45678,'Daphney Duck',1),
            (245678,'Pluto Dog',0);

            SELECT Number,
            MAX(CASE WHEN Seq = 0 THEN [Name] END) as Owner1,
            MAX(CASE WHEN Seq = 1 Then [Name] END) AS Owner2
            FROM dbo.Customer
            GROUP BY Number;


            Result



            Number Owner1 Owner2
            12345 Mickey Mouse Minnie Mouse
            45678 Donald Duck Daphney Duck
            245678 Pluto Dog NULL



            Update: Issue with many columns



            If the other columns mentioned in the comments are the same based on the number, you can add them to the group by, and cast the TEXT value as varchar(4000) (max 3640 datalength) Otherwise you would have to choose one of the two with MAX / MIN / ...



            SELECT Number, 
            MAX(CASE WHEN Seq = 0 THEN [Name] END) as Owner1,
            MAX(CASE WHEN Seq = 1 Then [Name] END) AS Owner2
            ,[Street1], [Street2], [City] , [State] , [Zipcode] , [HomePhone] ,CAST(JobMemo as varchar(4000))
            FROM dbo.Debtors
            GROUP BY Number,[Street1], [Street2], [City] , [State] , [Zipcode] , [HomePhone] ,CAST(JobMemo as varchar(4000));


            Disclaimer: the performance will probably not be optimal






            share|improve this answer

























            • OK thank you! So there's pretty much no way to run a simle query without creating a table first?

              – Nicole Montez
              2 hours ago











            • @NicoleMontez No problem, I am not sure what you mean by the 'without creating a table first'? I could have created a temp table aswell. As far as questions go, giving the sample data + table create statement really helps! We always have to create a table with the same sample data to show a valid resultset as an answer :).

              – Randi Vertongen
              2 hours ago












            • Error on Pivot table: Pivot grouping columns must be comparable. The type of column "JobMemo" is "text", which is not comparable. CREATE TABLE [dbo].[Debtors]( [Number] [int] NOT NULL, [Seq] [int] NULL, [Name] [varchar](30) NULL, [Street1] [varchar](30) NULL, [Street2] [varchar](30) NULL, [City] [varchar](30) NULL, [State] [varchar](3) NULL, [Zipcode] [varchar](10) NULL, [HomePhone] [varchar](30) NULL,

              – Nicole Montez
              2 hours ago












            • @NicoleMontez does the JobMemo need to be text? What does SELECT MAX(DATALENGTH(JobMemo)) from dbo.Debtors return? Maybe you could change it to varchar or nvarchar datatype? You could also cast it in the query. Could you add the entire table to the question?

              – Randi Vertongen
              1 hour ago












            • It's HUGE. I couldn't fit all of the characters. I'm sorry, this is my first question. I normally just wing it on my own. I inherited this table, and it's very invloved.

              – Nicole Montez
              1 hour ago











            Your Answer








            StackExchange.ready(function()
            var channelOptions =
            tags: "".split(" "),
            id: "182"
            ;
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function()
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled)
            StackExchange.using("snippets", function()
            createEditor();
            );

            else
            createEditor();

            );

            function createEditor()
            StackExchange.prepareEditor(
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: false,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: null,
            bindNavPrevention: true,
            postfix: "",
            imageUploader:
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            ,
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            );



            );






            Nicole Montez is a new contributor. Be nice, and check out our Code of Conduct.









            draft saved

            draft discarded


















            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234964%2fselecting-the-same-column-from-different-rows-based-on-different-criteria%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown

























            2 Answers
            2






            active

            oldest

            votes








            2 Answers
            2






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            2














            Given this table and data:



            CREATE TABLE #src(Number int, name varchar(32), seq bit);

            INSERT #src(Number,name,seq) VALUES
            (12345,'Mickey Mouse',0),
            (12345,'Minnie Mouse',1),
            (45678,'Donald Duck',0),
            (45678,'Daphney Duck',1),
            (245678,'Pluto Dog',0);


            You can apply a simple PIVOT:



            SELECT Number, Owner1 = [0], Owner2 = COALESCE([1],'')
            FROM #src AS c
            PIVOT (MAX(name) FOR seq IN ([0],[1])) AS p
            ORDER BY Number;


            Results:



            number Owner1 Owner2
            ------ ------------ ------------
            12345 Mickey Mouse Minnie Mouse
            45678 Donald Duck Daphney Duck
            245678 Pluto Dog


            I would just ensure that Number, seq is enforced to be unique and that seq is either a bit or has a constraint so that it can only be 0 or 1.






            share|improve this answer





























              2














              Given this table and data:



              CREATE TABLE #src(Number int, name varchar(32), seq bit);

              INSERT #src(Number,name,seq) VALUES
              (12345,'Mickey Mouse',0),
              (12345,'Minnie Mouse',1),
              (45678,'Donald Duck',0),
              (45678,'Daphney Duck',1),
              (245678,'Pluto Dog',0);


              You can apply a simple PIVOT:



              SELECT Number, Owner1 = [0], Owner2 = COALESCE([1],'')
              FROM #src AS c
              PIVOT (MAX(name) FOR seq IN ([0],[1])) AS p
              ORDER BY Number;


              Results:



              number Owner1 Owner2
              ------ ------------ ------------
              12345 Mickey Mouse Minnie Mouse
              45678 Donald Duck Daphney Duck
              245678 Pluto Dog


              I would just ensure that Number, seq is enforced to be unique and that seq is either a bit or has a constraint so that it can only be 0 or 1.






              share|improve this answer



























                2












                2








                2







                Given this table and data:



                CREATE TABLE #src(Number int, name varchar(32), seq bit);

                INSERT #src(Number,name,seq) VALUES
                (12345,'Mickey Mouse',0),
                (12345,'Minnie Mouse',1),
                (45678,'Donald Duck',0),
                (45678,'Daphney Duck',1),
                (245678,'Pluto Dog',0);


                You can apply a simple PIVOT:



                SELECT Number, Owner1 = [0], Owner2 = COALESCE([1],'')
                FROM #src AS c
                PIVOT (MAX(name) FOR seq IN ([0],[1])) AS p
                ORDER BY Number;


                Results:



                number Owner1 Owner2
                ------ ------------ ------------
                12345 Mickey Mouse Minnie Mouse
                45678 Donald Duck Daphney Duck
                245678 Pluto Dog


                I would just ensure that Number, seq is enforced to be unique and that seq is either a bit or has a constraint so that it can only be 0 or 1.






                share|improve this answer















                Given this table and data:



                CREATE TABLE #src(Number int, name varchar(32), seq bit);

                INSERT #src(Number,name,seq) VALUES
                (12345,'Mickey Mouse',0),
                (12345,'Minnie Mouse',1),
                (45678,'Donald Duck',0),
                (45678,'Daphney Duck',1),
                (245678,'Pluto Dog',0);


                You can apply a simple PIVOT:



                SELECT Number, Owner1 = [0], Owner2 = COALESCE([1],'')
                FROM #src AS c
                PIVOT (MAX(name) FOR seq IN ([0],[1])) AS p
                ORDER BY Number;


                Results:



                number Owner1 Owner2
                ------ ------------ ------------
                12345 Mickey Mouse Minnie Mouse
                45678 Donald Duck Daphney Duck
                245678 Pluto Dog


                I would just ensure that Number, seq is enforced to be unique and that seq is either a bit or has a constraint so that it can only be 0 or 1.







                share|improve this answer














                share|improve this answer



                share|improve this answer








                edited 2 hours ago

























                answered 2 hours ago









                Aaron BertrandAaron Bertrand

                154k18299495




                154k18299495























                    1














                    Another method without using pivot



                    CREATE TABLE dbo.Customer(Number int, [Name] varchar(255),Seq bit);
                    INSERT INTO dbo.Customer(Number,[Name],Seq)
                    VALUES
                    (12345,'Mickey Mouse',0),
                    (12345,'Minnie Mouse',1),
                    (45678,'Donald Duck',0),
                    (45678,'Daphney Duck',1),
                    (245678,'Pluto Dog',0);

                    SELECT Number,
                    MAX(CASE WHEN Seq = 0 THEN [Name] END) as Owner1,
                    MAX(CASE WHEN Seq = 1 Then [Name] END) AS Owner2
                    FROM dbo.Customer
                    GROUP BY Number;


                    Result



                    Number Owner1 Owner2
                    12345 Mickey Mouse Minnie Mouse
                    45678 Donald Duck Daphney Duck
                    245678 Pluto Dog NULL



                    Update: Issue with many columns



                    If the other columns mentioned in the comments are the same based on the number, you can add them to the group by, and cast the TEXT value as varchar(4000) (max 3640 datalength) Otherwise you would have to choose one of the two with MAX / MIN / ...



                    SELECT Number, 
                    MAX(CASE WHEN Seq = 0 THEN [Name] END) as Owner1,
                    MAX(CASE WHEN Seq = 1 Then [Name] END) AS Owner2
                    ,[Street1], [Street2], [City] , [State] , [Zipcode] , [HomePhone] ,CAST(JobMemo as varchar(4000))
                    FROM dbo.Debtors
                    GROUP BY Number,[Street1], [Street2], [City] , [State] , [Zipcode] , [HomePhone] ,CAST(JobMemo as varchar(4000));


                    Disclaimer: the performance will probably not be optimal






                    share|improve this answer

























                    • OK thank you! So there's pretty much no way to run a simle query without creating a table first?

                      – Nicole Montez
                      2 hours ago











                    • @NicoleMontez No problem, I am not sure what you mean by the 'without creating a table first'? I could have created a temp table aswell. As far as questions go, giving the sample data + table create statement really helps! We always have to create a table with the same sample data to show a valid resultset as an answer :).

                      – Randi Vertongen
                      2 hours ago












                    • Error on Pivot table: Pivot grouping columns must be comparable. The type of column "JobMemo" is "text", which is not comparable. CREATE TABLE [dbo].[Debtors]( [Number] [int] NOT NULL, [Seq] [int] NULL, [Name] [varchar](30) NULL, [Street1] [varchar](30) NULL, [Street2] [varchar](30) NULL, [City] [varchar](30) NULL, [State] [varchar](3) NULL, [Zipcode] [varchar](10) NULL, [HomePhone] [varchar](30) NULL,

                      – Nicole Montez
                      2 hours ago












                    • @NicoleMontez does the JobMemo need to be text? What does SELECT MAX(DATALENGTH(JobMemo)) from dbo.Debtors return? Maybe you could change it to varchar or nvarchar datatype? You could also cast it in the query. Could you add the entire table to the question?

                      – Randi Vertongen
                      1 hour ago












                    • It's HUGE. I couldn't fit all of the characters. I'm sorry, this is my first question. I normally just wing it on my own. I inherited this table, and it's very invloved.

                      – Nicole Montez
                      1 hour ago















                    1














                    Another method without using pivot



                    CREATE TABLE dbo.Customer(Number int, [Name] varchar(255),Seq bit);
                    INSERT INTO dbo.Customer(Number,[Name],Seq)
                    VALUES
                    (12345,'Mickey Mouse',0),
                    (12345,'Minnie Mouse',1),
                    (45678,'Donald Duck',0),
                    (45678,'Daphney Duck',1),
                    (245678,'Pluto Dog',0);

                    SELECT Number,
                    MAX(CASE WHEN Seq = 0 THEN [Name] END) as Owner1,
                    MAX(CASE WHEN Seq = 1 Then [Name] END) AS Owner2
                    FROM dbo.Customer
                    GROUP BY Number;


                    Result



                    Number Owner1 Owner2
                    12345 Mickey Mouse Minnie Mouse
                    45678 Donald Duck Daphney Duck
                    245678 Pluto Dog NULL



                    Update: Issue with many columns



                    If the other columns mentioned in the comments are the same based on the number, you can add them to the group by, and cast the TEXT value as varchar(4000) (max 3640 datalength) Otherwise you would have to choose one of the two with MAX / MIN / ...



                    SELECT Number, 
                    MAX(CASE WHEN Seq = 0 THEN [Name] END) as Owner1,
                    MAX(CASE WHEN Seq = 1 Then [Name] END) AS Owner2
                    ,[Street1], [Street2], [City] , [State] , [Zipcode] , [HomePhone] ,CAST(JobMemo as varchar(4000))
                    FROM dbo.Debtors
                    GROUP BY Number,[Street1], [Street2], [City] , [State] , [Zipcode] , [HomePhone] ,CAST(JobMemo as varchar(4000));


                    Disclaimer: the performance will probably not be optimal






                    share|improve this answer

























                    • OK thank you! So there's pretty much no way to run a simle query without creating a table first?

                      – Nicole Montez
                      2 hours ago











                    • @NicoleMontez No problem, I am not sure what you mean by the 'without creating a table first'? I could have created a temp table aswell. As far as questions go, giving the sample data + table create statement really helps! We always have to create a table with the same sample data to show a valid resultset as an answer :).

                      – Randi Vertongen
                      2 hours ago












                    • Error on Pivot table: Pivot grouping columns must be comparable. The type of column "JobMemo" is "text", which is not comparable. CREATE TABLE [dbo].[Debtors]( [Number] [int] NOT NULL, [Seq] [int] NULL, [Name] [varchar](30) NULL, [Street1] [varchar](30) NULL, [Street2] [varchar](30) NULL, [City] [varchar](30) NULL, [State] [varchar](3) NULL, [Zipcode] [varchar](10) NULL, [HomePhone] [varchar](30) NULL,

                      – Nicole Montez
                      2 hours ago












                    • @NicoleMontez does the JobMemo need to be text? What does SELECT MAX(DATALENGTH(JobMemo)) from dbo.Debtors return? Maybe you could change it to varchar or nvarchar datatype? You could also cast it in the query. Could you add the entire table to the question?

                      – Randi Vertongen
                      1 hour ago












                    • It's HUGE. I couldn't fit all of the characters. I'm sorry, this is my first question. I normally just wing it on my own. I inherited this table, and it's very invloved.

                      – Nicole Montez
                      1 hour ago













                    1












                    1








                    1







                    Another method without using pivot



                    CREATE TABLE dbo.Customer(Number int, [Name] varchar(255),Seq bit);
                    INSERT INTO dbo.Customer(Number,[Name],Seq)
                    VALUES
                    (12345,'Mickey Mouse',0),
                    (12345,'Minnie Mouse',1),
                    (45678,'Donald Duck',0),
                    (45678,'Daphney Duck',1),
                    (245678,'Pluto Dog',0);

                    SELECT Number,
                    MAX(CASE WHEN Seq = 0 THEN [Name] END) as Owner1,
                    MAX(CASE WHEN Seq = 1 Then [Name] END) AS Owner2
                    FROM dbo.Customer
                    GROUP BY Number;


                    Result



                    Number Owner1 Owner2
                    12345 Mickey Mouse Minnie Mouse
                    45678 Donald Duck Daphney Duck
                    245678 Pluto Dog NULL



                    Update: Issue with many columns



                    If the other columns mentioned in the comments are the same based on the number, you can add them to the group by, and cast the TEXT value as varchar(4000) (max 3640 datalength) Otherwise you would have to choose one of the two with MAX / MIN / ...



                    SELECT Number, 
                    MAX(CASE WHEN Seq = 0 THEN [Name] END) as Owner1,
                    MAX(CASE WHEN Seq = 1 Then [Name] END) AS Owner2
                    ,[Street1], [Street2], [City] , [State] , [Zipcode] , [HomePhone] ,CAST(JobMemo as varchar(4000))
                    FROM dbo.Debtors
                    GROUP BY Number,[Street1], [Street2], [City] , [State] , [Zipcode] , [HomePhone] ,CAST(JobMemo as varchar(4000));


                    Disclaimer: the performance will probably not be optimal






                    share|improve this answer















                    Another method without using pivot



                    CREATE TABLE dbo.Customer(Number int, [Name] varchar(255),Seq bit);
                    INSERT INTO dbo.Customer(Number,[Name],Seq)
                    VALUES
                    (12345,'Mickey Mouse',0),
                    (12345,'Minnie Mouse',1),
                    (45678,'Donald Duck',0),
                    (45678,'Daphney Duck',1),
                    (245678,'Pluto Dog',0);

                    SELECT Number,
                    MAX(CASE WHEN Seq = 0 THEN [Name] END) as Owner1,
                    MAX(CASE WHEN Seq = 1 Then [Name] END) AS Owner2
                    FROM dbo.Customer
                    GROUP BY Number;


                    Result



                    Number Owner1 Owner2
                    12345 Mickey Mouse Minnie Mouse
                    45678 Donald Duck Daphney Duck
                    245678 Pluto Dog NULL



                    Update: Issue with many columns



                    If the other columns mentioned in the comments are the same based on the number, you can add them to the group by, and cast the TEXT value as varchar(4000) (max 3640 datalength) Otherwise you would have to choose one of the two with MAX / MIN / ...



                    SELECT Number, 
                    MAX(CASE WHEN Seq = 0 THEN [Name] END) as Owner1,
                    MAX(CASE WHEN Seq = 1 Then [Name] END) AS Owner2
                    ,[Street1], [Street2], [City] , [State] , [Zipcode] , [HomePhone] ,CAST(JobMemo as varchar(4000))
                    FROM dbo.Debtors
                    GROUP BY Number,[Street1], [Street2], [City] , [State] , [Zipcode] , [HomePhone] ,CAST(JobMemo as varchar(4000));


                    Disclaimer: the performance will probably not be optimal







                    share|improve this answer














                    share|improve this answer



                    share|improve this answer








                    edited 1 hour ago

























                    answered 2 hours ago









                    Randi VertongenRandi Vertongen

                    4,9361924




                    4,9361924












                    • OK thank you! So there's pretty much no way to run a simle query without creating a table first?

                      – Nicole Montez
                      2 hours ago











                    • @NicoleMontez No problem, I am not sure what you mean by the 'without creating a table first'? I could have created a temp table aswell. As far as questions go, giving the sample data + table create statement really helps! We always have to create a table with the same sample data to show a valid resultset as an answer :).

                      – Randi Vertongen
                      2 hours ago












                    • Error on Pivot table: Pivot grouping columns must be comparable. The type of column "JobMemo" is "text", which is not comparable. CREATE TABLE [dbo].[Debtors]( [Number] [int] NOT NULL, [Seq] [int] NULL, [Name] [varchar](30) NULL, [Street1] [varchar](30) NULL, [Street2] [varchar](30) NULL, [City] [varchar](30) NULL, [State] [varchar](3) NULL, [Zipcode] [varchar](10) NULL, [HomePhone] [varchar](30) NULL,

                      – Nicole Montez
                      2 hours ago












                    • @NicoleMontez does the JobMemo need to be text? What does SELECT MAX(DATALENGTH(JobMemo)) from dbo.Debtors return? Maybe you could change it to varchar or nvarchar datatype? You could also cast it in the query. Could you add the entire table to the question?

                      – Randi Vertongen
                      1 hour ago












                    • It's HUGE. I couldn't fit all of the characters. I'm sorry, this is my first question. I normally just wing it on my own. I inherited this table, and it's very invloved.

                      – Nicole Montez
                      1 hour ago

















                    • OK thank you! So there's pretty much no way to run a simle query without creating a table first?

                      – Nicole Montez
                      2 hours ago











                    • @NicoleMontez No problem, I am not sure what you mean by the 'without creating a table first'? I could have created a temp table aswell. As far as questions go, giving the sample data + table create statement really helps! We always have to create a table with the same sample data to show a valid resultset as an answer :).

                      – Randi Vertongen
                      2 hours ago












                    • Error on Pivot table: Pivot grouping columns must be comparable. The type of column "JobMemo" is "text", which is not comparable. CREATE TABLE [dbo].[Debtors]( [Number] [int] NOT NULL, [Seq] [int] NULL, [Name] [varchar](30) NULL, [Street1] [varchar](30) NULL, [Street2] [varchar](30) NULL, [City] [varchar](30) NULL, [State] [varchar](3) NULL, [Zipcode] [varchar](10) NULL, [HomePhone] [varchar](30) NULL,

                      – Nicole Montez
                      2 hours ago












                    • @NicoleMontez does the JobMemo need to be text? What does SELECT MAX(DATALENGTH(JobMemo)) from dbo.Debtors return? Maybe you could change it to varchar or nvarchar datatype? You could also cast it in the query. Could you add the entire table to the question?

                      – Randi Vertongen
                      1 hour ago












                    • It's HUGE. I couldn't fit all of the characters. I'm sorry, this is my first question. I normally just wing it on my own. I inherited this table, and it's very invloved.

                      – Nicole Montez
                      1 hour ago
















                    OK thank you! So there's pretty much no way to run a simle query without creating a table first?

                    – Nicole Montez
                    2 hours ago





                    OK thank you! So there's pretty much no way to run a simle query without creating a table first?

                    – Nicole Montez
                    2 hours ago













                    @NicoleMontez No problem, I am not sure what you mean by the 'without creating a table first'? I could have created a temp table aswell. As far as questions go, giving the sample data + table create statement really helps! We always have to create a table with the same sample data to show a valid resultset as an answer :).

                    – Randi Vertongen
                    2 hours ago






                    @NicoleMontez No problem, I am not sure what you mean by the 'without creating a table first'? I could have created a temp table aswell. As far as questions go, giving the sample data + table create statement really helps! We always have to create a table with the same sample data to show a valid resultset as an answer :).

                    – Randi Vertongen
                    2 hours ago














                    Error on Pivot table: Pivot grouping columns must be comparable. The type of column "JobMemo" is "text", which is not comparable. CREATE TABLE [dbo].[Debtors]( [Number] [int] NOT NULL, [Seq] [int] NULL, [Name] [varchar](30) NULL, [Street1] [varchar](30) NULL, [Street2] [varchar](30) NULL, [City] [varchar](30) NULL, [State] [varchar](3) NULL, [Zipcode] [varchar](10) NULL, [HomePhone] [varchar](30) NULL,

                    – Nicole Montez
                    2 hours ago






                    Error on Pivot table: Pivot grouping columns must be comparable. The type of column "JobMemo" is "text", which is not comparable. CREATE TABLE [dbo].[Debtors]( [Number] [int] NOT NULL, [Seq] [int] NULL, [Name] [varchar](30) NULL, [Street1] [varchar](30) NULL, [Street2] [varchar](30) NULL, [City] [varchar](30) NULL, [State] [varchar](3) NULL, [Zipcode] [varchar](10) NULL, [HomePhone] [varchar](30) NULL,

                    – Nicole Montez
                    2 hours ago














                    @NicoleMontez does the JobMemo need to be text? What does SELECT MAX(DATALENGTH(JobMemo)) from dbo.Debtors return? Maybe you could change it to varchar or nvarchar datatype? You could also cast it in the query. Could you add the entire table to the question?

                    – Randi Vertongen
                    1 hour ago






                    @NicoleMontez does the JobMemo need to be text? What does SELECT MAX(DATALENGTH(JobMemo)) from dbo.Debtors return? Maybe you could change it to varchar or nvarchar datatype? You could also cast it in the query. Could you add the entire table to the question?

                    – Randi Vertongen
                    1 hour ago














                    It's HUGE. I couldn't fit all of the characters. I'm sorry, this is my first question. I normally just wing it on my own. I inherited this table, and it's very invloved.

                    – Nicole Montez
                    1 hour ago





                    It's HUGE. I couldn't fit all of the characters. I'm sorry, this is my first question. I normally just wing it on my own. I inherited this table, and it's very invloved.

                    – Nicole Montez
                    1 hour ago










                    Nicole Montez is a new contributor. Be nice, and check out our Code of Conduct.









                    draft saved

                    draft discarded


















                    Nicole Montez is a new contributor. Be nice, and check out our Code of Conduct.












                    Nicole Montez is a new contributor. Be nice, and check out our Code of Conduct.











                    Nicole Montez is a new contributor. Be nice, and check out our Code of Conduct.














                    Thanks for contributing an answer to Database Administrators Stack Exchange!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid


                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.

                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function ()
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234964%2fselecting-the-same-column-from-different-rows-based-on-different-criteria%23new-answer', 'question_page');

                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    Popular posts from this blog

                    Are there any AGPL-style licences that require source code modifications to be public? Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern) Announcing the arrival of Valued Associate #679: Cesar Manara Unicorn Meta Zoo #1: Why another podcast?Force derivative works to be publicAre there any GPL like licenses for Apple App Store?Do you violate the GPL if you provide source code that cannot be compiled?GPL - is it distribution to use libraries in an appliance loaned to customers?Distributing App for free which uses GPL'ed codeModifications of server software under GPL, with web/CLI interfaceDoes using an AGPLv3-licensed library prevent me from dual-licensing my own source code?Can I publish only select code under GPLv3 from a private project?Is there published precedent regarding the scope of covered work that uses AGPL software?If MIT licensed code links to GPL licensed code what should be the license of the resulting binary program?If I use a public API endpoint that has its source code licensed under AGPL in my app, do I need to disclose my source?

                    2013 GY136 Descoberta | Órbita | Referências Menu de navegação«List Of Centaurs and Scattered-Disk Objects»«List of Known Trans-Neptunian Objects»

                    Mortes em março de 2019 Referências Menu de navegação«Zhores Alferov, Nobel de Física bielorrusso, morre aos 88 anos - Ciência»«Fallece Rafael Torija, o bispo emérito de Ciudad Real»«Peter Hurford dies at 88»«Keith Flint, vocalista do The Prodigy, morre aos 49 anos»«Luke Perry, ator de 'Barrados no baile' e 'Riverdale', morre aos 52 anos»«Former Rangers and Scotland captain Eric Caldow dies, aged 84»«Morreu, aos 61 anos, a antiga lenda do wrestling King Kong Bundy»«Fallece el actor y director teatral Abraham Stavans»«In Memoriam Guillaume Faye»«Sidney Sheinberg, a Force Behind Universal and Spielberg, Is Dead at 84»«Carmine Persico, Colombo Crime Family Boss, Is Dead at 85»«Dirigent Michael Gielen gestorben»«Ciclista tricampeã mundial e prata na Rio 2016 é encontrada morta em casa aos 23 anos»«Pagan Community Notes: Raven Grimassi dies, Indianapolis pop-up event cancelled, Circle Sanctuary announces new podcast, and more!»«Hal Blaine, Wrecking Crew Drummer, Dies at 90»«Morre Coutinho, que editou dupla lendária com Pelé no Santos»«Cantor Demétrius, ídolo da Jovem Guarda, morre em SP»«Ex-presidente do Vasco, Eurico Miranda morre no Rio de Janeiro»«Bronze no Mundial de basquete de 1971, Laís Elena morre aos 76 anos»«Diretor de Corridas da F1, Charlie Whiting morre aos 66 anos às vésperas do GP da Austrália»«Morreu o cardeal Danneels, da Bélgica»«Morreu o cartoonista Augusto Cid»«Morreu a atriz Maria Isabel de Lizandra, de "Vale Tudo" e novelas da Tupi»«WS Merwin, prize-winning poet of nature, dies at 91»«Atriz Márcia Real morre em São Paulo aos 88 anos»«Mauritanie: décès de l'ancien président Mohamed Mahmoud ould Louly»«Morreu Dick Dale, o rei da surf guitar e de "Pulp Fiction"»«Falleció Víctor Genes»«João Carlos Marinho, autor de 'O Gênio do Crime', morre em SP»«Legendary Horror Director and SFX Artist John Carl Buechler Dies at 66»«Morre em Salvador a religiosa Makota Valdina»«مرگ بازیکن‌ سابق نساجی بر اثر سقوط سنگ در مازندران»«Domingos Oliveira morre no Rio»«Morre Airton Ravagniani, ex-São Paulo, Fla, Vasco, Grêmio e Sport - Notícias»«Morre o escritor Flavio Moreira da Costa»«Larry Cohen, Writer-Director of 'It's Alive' and 'Hell Up in Harlem,' Dies at 77»«Scott Walker, experimental singer-songwriter, dead at 76»«Joseph Pilato, Day of the Dead Star and Horror Favorite, Dies at 70»«Sheffield United set to pay tribute to legendary goalkeeper Ted Burgin who has died at 91»«Morre Rafael Henzel, sobrevivente de acidente aéreo da Chapecoense»«Morre Valery Bykovsky, um dos primeiros cosmonautas da União Soviética»«Agnès Varda, cineasta da Nouvelle Vague, morre aos 90 anos»«Agnès Varda, cineasta francesa, morre aos 90 anos»«Tania Mallet, James Bond Actress and Helen Mirren's Cousin, Dies at 77»e