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

Understanding Ceva's Theorem

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

Is it fair for a professor to grade us on the possession of past papers?

List *all* the tuples!

What is Arya's weapon design?

Okay to merge included columns on otherwise identical indexes?

Withdrew £2800, but only £2000 shows as withdrawn on online banking; what are my obligations?

Error "illegal generic type for instanceof" when using local classes

What is the logic behind the Maharil's explanation of why we don't say שעשה ניסים on Pesach?

Why are there no cargo aircraft with "flying wing" design?

How to bypass password on Windows XP account?

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

How do pianists reach extremely loud dynamics?

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

Why am I getting the error "non-boolean type specified in a context where a condition is expected" for this request?

Should I discuss the type of campaign with my players?

Why light coming from distant stars is not discrete?

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

Book where humans were engineered with genes from animal species to survive hostile planets

What is known about the Ubaid lizard-people figurines?

String `!23` is replaced with `docker` in command line

How can I make names more distinctive without making them longer?

What exactly is a "Meth" in Altered Carbon?

When were vectors invented?



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 10 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 10 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
              9 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
              9 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
              9 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
              9 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
              9 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 9 hours ago

























                answered 10 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
                      9 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
                      9 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
                      9 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
                      9 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
                      9 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
                      9 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
                      9 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
                      9 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
                      9 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
                      9 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 9 hours ago

























                    answered 10 hours ago









                    Randi VertongenRandi Vertongen

                    4,9461924




                    4,9461924












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

                      – Nicole Montez
                      9 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
                      9 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
                      9 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
                      9 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
                      9 hours ago

















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

                      – Nicole Montez
                      9 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
                      9 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
                      9 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
                      9 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
                      9 hours ago
















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

                    – Nicole Montez
                    9 hours ago





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

                    – Nicole Montez
                    9 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
                    9 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
                    9 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
                    9 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
                    9 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
                    9 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
                    9 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
                    9 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
                    9 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

                    How to create a command for the “strange m” symbol in latex? Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern)How do you make your own symbol when Detexify fails?Writing bold small caps with mathpazo packageplus-minus symbol with parenthesis around the minus signGreek character in Beamer document titleHow to create dashed right arrow over symbol?Currency symbol: Turkish LiraDouble prec as a single symbol?Plus Sign Too Big; How to Call adfbullet?Is there a TeX macro for three-legged pi?How do I get my integral-like symbol to align like the integral?How to selectively substitute a letter with another symbol representing the same letterHow do I generate a less than symbol and vertical bar that are the same height?

                    Българска екзархия Съдържание История | Български екзарси | Вижте също | Външни препратки | Литература | Бележки | НавигацияУстав за управлението на българската екзархия. Цариград, 1870Слово на Ловешкия митрополит Иларион при откриването на Българския народен събор в Цариград на 23. II. 1870 г.Българската правда и гръцката кривда. От С. М. (= Софийски Мелетий). Цариград, 1872Предстоятели на Българската екзархияПодмененият ВеликденИнформационна агенция „Фокус“Димитър Ризов. Българите в техните исторически, етнографически и политически граници (Атлас съдържащ 40 карти). Berlin, Königliche Hoflithographie, Hof-Buch- und -Steindruckerei Wilhelm Greve, 1917Report of the International Commission to Inquire into the Causes and Conduct of the Balkan Wars

                    Category:Tremithousa Media in category "Tremithousa"Navigation menuUpload media34° 49′ 02.7″ N, 32° 26′ 37.32″ EOpenStreetMapGoogle EarthProximityramaReasonatorScholiaStatisticsWikiShootMe