Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2016
    Posts
    1

    Question Unanswered: Query Columns & Rows - Output Not Expected

    I want to sort a table, the first example to make it look like output in 2nd example. Also attached a photo for better clarification - if needed to understand what I need.Click image for larger version. 

Name:	SQL Sort Columns Rows.JPG 
Views:	10 
Size:	48.3 KB 
ID:	17069
    I tried with no luck...

    SELECT FROM *
    WHERE tbl_example
    ORDER BY Dsc

    SQL Query to Change From This

    A B C D
    |- - - - - - - - - - - - - - - - - |
    1 | ABC 7 10 2 |
    2 | DEF 5 12 11 |
    3 | GHI 3 4 13 |
    4 | JKL 1 6 15 |
    5 | MNO 9 8 14 |
    |- - - - - - - - - - - - - - - - - -|


    To This Order

    1 JKL
    2 ABC
    3 GHI
    4 GHI
    5 DEF
    6 JKL
    7 ABC
    8 MNO
    9 MNO
    10 ABC
    11 DEF
    12 DEF
    13 GHI
    14 MNO
    15 JKL

    Help is Appreciated (I am new). Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    off hand the only way I cazn see of handling this is to create views for each column B,C,D, each with A

    eg
    select A,B as other from mytable
    select A,C as other from mytable
    select A,D as other from mytable

    then do a union all query
    and sort on other
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Expanding on healdem's answer:
    Code:
    ; WITH a AS (
    SELECT a, b, c, d
       FROM (VALUES
       ('ABC', 7, 10,  2)
    ,  ('DEF', 5, 12, 11)
    ,  ('GHI', 3,  4, 13)
    ,  ('JKL', 1,  6, 15)
    ,  ('MNO', 9,  8, 14)
       ) AS v (a, b, c, d)
    ), b AS (
    SELECT b AS a, a AS b FROM a UNION ALL
    SELECT c, a FROM a UNION ALL
    SELECT d, a FROM a
    )
    SELECT a, b
       FROM b
       ORDER BY 1, 2
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    This can also be done (not too sure which SQL version, I suspect 2012) with CROSS APPLY and the VALUES operators:

    Code:
    create table #tab
    (a char(3),
     b int,
     c int,
     d int)
    insert into #tab values ('ABC', 7, 10, 2),
    ('DEF', 5, 12, 11),
    ('GHI', 3, 4, 13),
    ('JKL', 1, 6, 15),
    ('MNO', 9, 8, 14)
    
    select a, z
    from #tab cross apply
        (values (a, b), (a, c), (a, d)) as t(y, z) 
    order by z
    Curiously, the two answers have slightly different query plans, which may matter when you get to consuming millions of rows.

    I picked up this trick off of Stack Exchange some time ago, so I can't take full credit for coming up with this. Neat trick, though.
    Last edited by MCrowley; 08-08-16 at 12:34. Reason: Attribution of credit

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    If you have or create a 'Number' Table ( a Table with a column of sequential numbers), you can use that in a Join to get the results you want.
    Code:
    WITH NUM_TAB (SEQ_NUM)
      AS (
          SELECT 1 
          UNION ALL
          SELECT SEQ_NUM + 1
          FROM NUM_TAB
          WHERE SEQ_NUM + 1 < 20
         )
       , DATA_TAB (COLA, COLB, COLC, COLD)
      AS (
          SELECT 'ABC',  7, 10,  2 UNION ALL
          SELECT 'DEF',  5, 12, 11 UNION ALL
          SELECT 'GHI',  3,  4, 13 UNION ALL
          SELECT 'JKL',  1,  6, 15 UNION ALL
          SELECT 'MNO',  9,  8, 14 
         )
    SELECT SEQ_NUM, COLA
    FROM NUM_TAB
           INNER JOIN
         DATA_TAB
           ON SEQ_NUM IN(COLB, COLC, COLD)
    ORDER BY SEQ_NUM
     ;
    Code:
        SEQ_NUM COLA
    ----------- ----
              1 JKL
              2 ABC
              3 GHI
              4 GHI
              5 DEF
              6 JKL
              7 ABC
              8 MNO
              9 MNO
             10 ABC
             11 DEF
             12 DEF
             13 GHI
             14 MNO
             15 JKL
    
    (15 row(s) affected)
    As MCrowley mentioned, the method that works best for large tables will need to be determined by testing.

Tags for this Thread

Posting Permissions

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