Aggregate data in a row-like fashion

• qwerty.qwertz

  Grasshopper

  Points: 13

  More actions

  April 17, 2016 at 4:10 am

  #318123

  I have a SQL input table that is structured like the one shown below. Based on this input table I wanna create another table that aggregates the information in a row-like fashion as shown below. How can I do this for an arbitrary amount of values?

  Input table:

  ID | Name | Value | Order

  1 | Tom | A | 1

  2 | Jeff | B | 1

  3 | Max | B | 1

  4 | Tom | B | 2

  Output table:

  ID | Name | Val1 | Val2

  1 | Tom | A | B

  2 | Jeff | B | NULL

  3 | Max | B | NULL

• Jeff Moden

  SSC Guru

  Points: 1004659

  More actions

  April 17, 2016 at 8:42 am

  #1872599

  qwerty.qwertz (4/17/2016)

  ---

  I have a SQL input table that is structured like the one shown below. Based on this input table I wanna create another table that aggregates the information in a row-like fashion as shown below. How can I do this for an arbitrary amount of values?

  Input table:

  ID | Name | Value | Order

  1 | Tom | A | 1

  2 | Jeff | B | 1

  3 | Max | B | 1

  4 | Tom | B | 2

  Output table:

  ID | Name | Val1 | Val2

  1 | Tom | A | B

  2 | Jeff | B | NULL

  3 | Max | B | NULL

  The answer is to create a dynamic CROSSTAB or PIVOT (the CROSSTAB is usually faster). If you want a coded answer, please read and heed the article at the first link in my signature line below under "Helpful Links". It'll help us help you much better.

  Unless this is for some type of reporting, storing such denormalized data in a table is considered to be one of the worst practices there is, especially if the number of columns is going to be dynamic. I appreciate the simplification of whatever problem you're trying to solve but a bit more detail, along with readily consumable data as demonstrated in the article I referred you to, will allow us to help you better.

  If you'd rather not do that for some reason, then here's an article that introduces the nature of a dynamic CROSSTAB. Just substitute MAX for SUM in those places where you want to pivot character based data.

  http://www.sqlservercentral.com/articles/Crosstab/65048/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)