在sql server 2008中将列数据显示为标题

I have a table like:

我有一张桌子:

-------------------
id  class name      sub 
------------------------
1   mca   aditya    network
2   mca   abhishek  daa
3   mca   akhilesh  algorithm
4   btech ram       cs
5   btech shyam     ds
6   btech anand     client/server
7   mba   furqan    os
8   mba   arvind    marketing
9   mba   aayush    hr

I want a result set like the following:

我想要一个如下的结果集:

----------------
class    name      sub
------------------------
mca      aditya    network
         abhishek  daa
         akhilesh  algorithm
btech    ram       cs
         shyam     ds
         anand     client/server
mba      furqan    os
         arvind    marketing
         aayush    hr

2 个解决方案

#1

I think you may be better off handling this in your UI / Display tier, but if you need the query to do it in SQL 2008, you can use a CTE with ROW_NUMBER()

我认为您可能最好在UI /显示层中处理此问题,但如果您需要在SQL 2008中执行此查询,则可以使用带有ROW_NUMBER()的CTE

WITH ordered AS
(
   SELECT t.class, t.name, t.sub
      , ROW_NUMBER() OVER (PARTITION BY t.class ORDER BY t.Name ASC) AS RowNumber
   FROM myTable AS t
)
SELECT CASE 
          WHEN o.RowNumber = 1 THEN o.class
          ELSE ''
       END AS class
   , o.name, o.sub
FROM ordered AS o

I'm not sure what sort / order you're looking for. Your example appears to still be ordered by Id. If you want this, you can add the Id column into the ordered CTE and then add ORDER BY o.Id ASC at the end of the query.

我不确定你要找的是什么类型/顺序。您的示例似乎仍由Id订购。如果需要,可以将Id列添加到有序CTE中,然后在查询结尾处添加ORDER BY o.Id ASC。

#2

Adam's answer will work, but the most correct part of his answer is

亚当的答案是有效的,但他答案中最正确的部分是

you may be better off handling this in your UI / Display tier

您可能最好在UI /显示层中处理此问题

What you are trying to do is - to put it bluntly - wrong. The below says, for example, that aditya has class mca, but abhishek and akhilesh don't have a class.

你要做的是 - 直言不讳 - 错了。下面说,例如,aditya有类mca,但abhishek和akhilesh没有类。

class    name      sub 
--------------------- 
mca      aditya    network          
         abhishek  daa          
         akhilesh  algorithm 
btech    ram       cs              
         shyam     ds
         anand     client/server

Is it true that abhishek and akhilesh don't have a class? Of course not.

abhishek和akhilesh没有班级是真的吗?当然不是。

So if I have an application that calls this stored procedure or view and looks at this resultset, how am I supposed to know which class shyam belongs to? You can't say 'it's btech because that's the last class mentioned', because how do you know which order the results are viewed? You have no order by in your query.

因此,如果我有一个调用此存储过程或视图的应用程序并查看此结果集,我应该如何知道shyam属于哪个类?你不能说'这是btech,因为这是最后提到的课程',因为你怎么知道结果被查看的顺序是什么?您的查询中没有订单。

#1