更改结果中列的顺序，而不更改选择顺序 [英] Change order of columns appearing in results, without changing select order

问题描述

我有一个查询，我选择了超过 15 项，因此在我的结果中获得(超过)15 列.我选择的一些东西是大 CASE 语句.T-SQL 当然会按照您在 SELECT 语句中列出内容的顺序显示您的结果.

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) 重新排序它们的选择方式(因为每次我想并排比较其他两列时都需要这样做)

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)

即不想改变:SELECT a,b,c,d,e,f 到 SELECT f,d,a,b,c,e因为 a-f 每个可以是 5-10 行代码

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) 在结果中彼此相邻的拖放列，因为如果我希望第 2 列紧挨着第 9 列和第 14 列，并且所有三个都在结果表的末尾，那么有很多事情要做.

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) 知道所选内容的列号而不是列名

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, *

T-SQL (SQL Server 2008) 中是否存在做最后一行的函数?

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

推荐答案

TSQL 中没有移动"列的功能，除了编辑 SELECT 列表顺序之外，这是您能做的最好的事情:

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 ....

您可以将现有查询包装在派生表中，然后您可以随意移动单行列名称.只需确保将任何 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.

如果您使用的是 SSMS，您可以在结果到网格"模式下查看结果集，只需拖放列标题即可左右滑动列.

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.

这篇关于更改结果中列的顺序，而不更改选择顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！