Change order of columns appearing in results, with

问题:

I have a query where I'm selecting more than 15 things, and thus getting (more than) 15 columns in my results. Some of the things I've selected are big CASE statements. T-SQL of course displays your result in the order than you list things in the SELECT statement.

Is there a way to have the result columns displayed in a different order to help with my review of them, without

a) re-ordering how they were selected (because I'll need to do this every time I want to compare two other columns side-by-side)

i.e. Don't want to change: SELECT a,b,c,d,e,f to SELECT f,d,a,b,c,e since a-f can each be 5-10 lines of code

b) drag-n-drop column in the results next to each other, because if I want column 2 to be next to column 9 and column 14, and all three to be at the end of the result table, then that'd a lot of dragging to do.

c) knowing the column number of what was selected as opposed to the column name

What I'm looking for is something that resembles:

Select
    vi.Name
   ,vi.Height
   ,vi.Power
   ,case when tt.losses < 3
         then 'Y'
         else 'N'
    end as MasteryKen
   ,tt.blahnum
   ,vi.blahtext
   ,vi.blahdate
   ,vi.blahcalc
   ,tt.blahflag
   ,vi.blahflag
   ,vi.blahcompare

From SenshiVitalInfo vi 
Join TatakauTable tt 
  on vi.namecd=tt.namecd

OrderOutputResults by tt.blahflag, vi.blahflag, *

Does a function to do the last line exist in T-SQL (SQL Server 2008)?

回答1:

There is no functionality in TSQL to "move" the columns around, other than editing the SELECT list order, this is the best you can do:

SELECT
    d.Name
   ,d.Height
   ,d.Power
   ,d.MasteryKen   --<<can now move around single lines
   ,d.blahnum
   ,d.blahtext
   ,d.blahdate
   ,d.blahcalc
   ,d.blahflag
   ,d.blahflag
   ,d.blahcompare
FROM (Select
          vi.Name
         ,vi.Height
         ,vi.Power
         ,case when tt.losses < 3
               then 'Y'
               else 'N'
          end as MasteryKen
         ,tt.blahnum
         ,vi.blahtext
         ,vi.blahdate
         ,vi.blahcalc
         ,tt.blahflag
         ,vi.blahflag
         ,vi.blahcompare

      From SenshiVitalInfo vi 
      Join TatakauTable tt 
        on vi.namecd=tt.namecd
     ) d
--ORDER BY ....

You can wrap your existing query inside a derived table, where you can then move the single line columns names all you want. Just make sure that any ORDER BY is moved out of the derived table.

If You are using SSMS, you can view your result set in "results to grid" mode and just drag and drop the column headings to slide the columns around.

回答2:

No this does not exist.

If you don't like moving the case statements you could put the query into a view that you select from and then you select list will be simplified, but that seems like a waste of effort to me.

回答3:

I don't think this functionality is part of any SQL variant.

If you are using a SQL IDE to view the result set you might be able to drag the columns you want together, I used to do this in SSMS.

Otherwise Abe's answer is all you can do.