How to show query result columnar(in different col

问题:

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

this is a simple query:

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

but I want following result:(I want to show all classes of every flight and capacity in one single row but in different columns)

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

回答1:

Maybe something like this:

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

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