Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: 12 columns => 2 columns (SQL command or Unpivot?)

  1. #1
    New Member
    Join Date
    Apr 2015
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 12 columns => 2 columns (SQL command or Unpivot?)

    Hello!!!

    I have a table connected to SQL that outputs a column for each month.
    For working it better in Excel, I need to transforme that 12 columns to only 2... a first one for the values and the second for the respective month (that at this moment are the column titles).

    In your opinion, which will be the better option (or if it dependes, advantages and desavantages):
    a) a SQL command (not familiar to me);
    b) use unpivot in Query Edit

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,380
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    1 Thread(s)

    Default Re: 12 columns => 2 columns (SQL command or Unpivot?)

    in sql you can

    item + ' ' + item

    you apply your sequence with

    ORDER BY
    10, 4 5, 7
    Last edited by mole999; Mar 14th, 2017 at 02:27 AM.
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,308
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default Re: 12 columns => 2 columns (SQL command or Unpivot?)


  4. #4
    New Member
    Join Date
    Apr 2015
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 12 columns => 2 columns (SQL command or Unpivot?)

    Quote Originally Posted by mole999 View Post
    in sql you can

    item + ' ' + item

    you apply your sequence with

    ORDER BY
    10, 4 5, 7
    But that will sum all the values, correct? I still need the values for each month, so one row with JAN, FEV (...) NOV e DEZ will become twelve lines (I exchange clumns by rows)

  5. #5
    New Member
    Join Date
    Apr 2015
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 12 columns => 2 columns (SQL command or Unpivot?)

    Quote Originally Posted by Marcelo Branco View Post
    Thank you.

    The output is exactly that, but I need something more dynamic, since it will be something used monthly.

  6. #6
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,380
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    1 Thread(s)

    Default Re: 12 columns => 2 columns (SQL command or Unpivot?)

    no sum. it would bring together fields with a space
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  7. #7
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,626
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    4 Thread(s)

    Default Re: 12 columns => 2 columns (SQL command or Unpivot?)

    You should be able to use SQL to transpose columns/rows, perhaps using UNPIVOT.
    If posting code please use code tags.

  8. #8
    New Member
    Join Date
    Apr 2015
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 12 columns => 2 columns (SQL command or Unpivot?)

    Quote Originally Posted by mole999 View Post
    no sum. it would bring together fields with a space
    But I do not want all field in only one cell. I want them in different cells, but horizontally.
    (sorry in I do not understand correctly)

  9. #9
    New Member
    Join Date
    Apr 2015
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 12 columns => 2 columns (SQL command or Unpivot?)

    Quote Originally Posted by Norie View Post
    You should be able to use SQL to transpose columns/rows, perhaps using UNPIVOT.
    I was trying to use UNPIVOT in SQL, but my poor knowledge don't allow me. Do you have any example that I can use?

  10. #10
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,308
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default Re: 12 columns => 2 columns (SQL command or Unpivot?)

    Quote Originally Posted by flpnevs View Post
    Thank you.

    The output is exactly that, but I need something more dynamic, since it will be something used monthly.
    See if this helps
    Excel Tips From John Walkenbach: Creating A Database Table From A Summary Table

    scroll down and you see a link to a macro

    M.
    Last edited by Marcelo Branco; Mar 14th, 2017 at 05:05 PM.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •