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

What is Wonderstone and are there any references to it pre-1982?

At the end of Thor: Ragnarok why don't the Asgardians turn and head for the Bifrost as per their original plan?

How do I stop a creek from eroding my steep embankment?

Generate an RGB colour grid

List of Python versions

Simplicity of the roots of a minimal polynomial

Check which numbers satisfy the condition [A*B*C = A! + B! + C!]

The logistics of corpse disposal

How to bypass password on Windows XP account?

What causes the vertical darker bands in my photo?

How to deal with a team lead who never gives me credit?

Apollo command module space walk?

Why do people hide their license plates in the EU?

Is it ethical to give a final exam after the professor has quit before teaching the remaining chapters of the course?

Why didn't this character "real die" when they blew their stack out in Altered Carbon?

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

Why is "Consequences inflicted." not a sentence?

51k Euros annually for a family of 4 in Berlin: Is it enough?

Why aren't air breathing engines used as small first stages

Bete Noir -- no dairy

Align equal signs while including text over equalities

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

What's the purpose of writing one's academic biography in the third person?

Why do we bend a book to keep it straight?



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 3 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 3 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
              3 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
              3 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
              3 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
              2 hours 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
              2 hours 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 3 hours ago

























                answered 3 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
                      3 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
                      3 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
                      3 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
                      2 hours 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
                      2 hours 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
                      3 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
                      3 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
                      3 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
                      2 hours 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
                      2 hours 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 2 hours ago

























                    answered 3 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
                      3 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
                      3 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
                      3 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
                      2 hours 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
                      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
                      3 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
                      3 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
                      3 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
                      2 hours 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
                      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
                    3 hours ago





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

                    – Nicole Montez
                    3 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
                    3 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
                    3 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
                    3 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
                    3 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
                    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
                    2 hours 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
                    2 hours 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
                    2 hours 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

                    名間水力發電廠 目录 沿革 設施 鄰近設施 註釋 外部連結 导航菜单23°50′10″N 120°42′41″E / 23.83611°N 120.71139°E / 23.83611; 120.7113923°50′10″N 120°42′41″E / 23.83611°N 120.71139°E / 23.83611; 120.71139計畫概要原始内容臺灣第一座BOT 模式開發的水力發電廠-名間水力電廠名間水力發電廠 水利署首件BOT案原始内容《小檔案》名間電廠 首座BOT水力發電廠原始内容名間電廠BOT - 經濟部水利署中區水資源局

                    Prove that NP is closed under karp reduction?Space(n) not closed under Karp reductions - what about NTime(n)?Class P is closed under rotation?Prove or disprove that $NL$ is closed under polynomial many-one reductions$mathbfNC_2$ is closed under log-space reductionOn Karp reductionwhen can I know if a class (complexity) is closed under reduction (cook/karp)Check if class $PSPACE$ is closed under polyonomially space reductionIs NPSPACE also closed under polynomial-time reduction and under log-space reduction?Prove PSPACE is closed under complement?Prove PSPACE is closed under union?

                    Is my guitar’s action too high? Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern)Strings too stiff on a recently purchased acoustic guitar | Cort AD880CEIs the action of my guitar really high?Μy little finger is too weak to play guitarWith guitar, how long should I give my fingers to strengthen / callous?When playing a fret the guitar sounds mutedPlaying (Barre) chords up the guitar neckI think my guitar strings are wound too tight and I can't play barre chordsF barre chord on an SG guitarHow to find to the right strings of a barre chord by feel?High action on higher fret on my steel acoustic guitar