如何显示查询结果的列状(在不同的列)，而不是逐行显示? [英] How to show query result columnar(in different columns) instead of row by row?

问题描述

我在另一个帖子中问了这个问题，但我没有改变. 我有三个表:

I asked this question in a different post but i t has been changed. I have three tables:

Flight table
 FlightId  int
 FlightNumber varchar(10)

FlightCapacity table
ID int
FlightIdRef int
ClassIdRef  int
Capacity    int

Class Table
ClassId  int
Name      varchar(10)

Class Table:
ClassId     Name
   1         Y
   2         A

Flight Table
FlightId   Number
1           123
2           423

FlightCapacity Table
Id   FlightIdRef    ClassIdref  Capacity
1       1              1          10
2       1              2          20
3       2              2          10

这是一个简单的查询:

select Flight.FlightNumber,Class.Name+RTRIM(FlightCapacity.Capacity)
   from  Flight
      inner join FlightCapacity
           on Flight.FlightId=FlightCapacity.FlightIdRef
               inner join Class
                    on FlightCapacity.ClassIdRef=Class.ClassId

但我想要以下结果:(我想在一行中但在不同列中显示每个航班和容量的所有类别)

FlightNumber     ClassNameAndCapacity1   ClassNameAndCapacity2    ClassNameAndCapacityn
  123                 Y10                         A20                      
  423                 Y10                         ---                      

推荐答案

也许是这样的:

首先提供一些测试数据:

First some test data:

CREATE TABLE Flight(FlightId  int,FlightNumber varchar(10))
CREATE TABLE FlightCapacity(ID int,FlightIdRef int,ClassIdRef int,Capacity int)
CREATE TABLE Class(ClassId int,Name varchar(10))

INSERT INTO Class VALUES(1,'Y'),(2,'A')
INSERT INTO Flight VALUES(1,123),(2,423)
INSERT INTO FlightCapacity VALUES(1,1,1,10),(2,1,2,20),(3,2,2,10)

然后，您必须获得像这样的唯一列:

Then you have to get the unique columns like this:

DECLARE @cols VARCHAR(MAX)
SELECT  @cols = COALESCE(@cols + ','+
                QUOTENAME('ClassNameAndCapacity'+CAST(ClassId AS VARCHAR(10))),
                QUOTENAME('ClassNameAndCapacity'+CAST(ClassId AS VARCHAR(10))))
FROM 
    Class

托管并执行动态sql:

The delcaring and execute the dynamic sql:

DECLARE @query NVARCHAR(4000)=
N'SELECT
    *
FROM
(
SELECT
    Flight.FlightNumber,
    Class.Name+CAST(FlightCapacity.Capacity AS VARCHAR(100)) AS ClassName,
    ''ClassNameAndCapacity''+CAST(Class.ClassId AS VARCHAR(10)) AS ClassAndCapacity
FROM
    Flight
    JOIN FlightCapacity
        ON Flight.FlightId=FlightCapacity.FlightIdRef
    JOIN Class
        ON FlightCapacity.ClassIdRef=Class.ClassId
) AS p
PIVOT
(
    MAX(ClassName)
    FOR ClassAndCapacity IN('+@cols+')
) AS pvt'

EXECUTE(@query)

然后以我为例，删除创建的表:

And then in my case I will drop the tables created:

DROP TABLE Flight
DROP TABLE Class
DROP TABLE FlightCapacity

这篇关于如何显示查询结果的列状(在不同的列)，而不是逐行显示?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！