你好，我有一些用户表格，他们都链接到一个旅程，所以不是显示每个用户在他们自己的一行中的旅程的选择，我想选择用户在一行
我可以使用此代码在选择中显示所有信息，但不知道如何将其排成一行。
CREATE TABLE Journeys (
`Journeypk` INT,
`mempickuppoint` VARCHAR(6) CHARACTER SET utf8,
`memdropoffpoint` VARCHAR(6) CHARACTER SET utf8,
`dteestimatedarrival` DATETIME,
`strreceivedby` VARCHAR(3) CHARACTER SET utf8
);
INSERT INTO Journeys VALUES
(1,'london','berlin','2019-08-17 00:00:00','jay');
CREATE TABLE Journeypassengers (
`passengerpk` INT,
`Journeyfk` INT,
`contactfk` INT
);
INSERT INTO Journeypassengers VALUES
(1,1,1),
(2,1,2),
(3,1,3);
CREATE TABLE Contacts (
`Contactpk` INT,
`name` VARCHAR(5) CHARACTER SET utf8,
`strreference` INT
);
INSERT INTO Contacts VALUES
(1,'sam',12412),
(2,'mark',7854674),
(3,'chloe',568345);
CREATE TABLE Vulnerbilityflags (
`Vulnerbilityflagspk` INT,
`strflag` VARCHAR(39) CHARACTER SET utf8,
`contactfk` INT
);
INSERT INTO Vulnerbilityflags VALUES
(1,'DO NOT VISIT ALONE',2),
(2,'Female Only',2),
(3,'Learning Disabilities',1),
(4,'Male Only',3),
(5,'Physical Disabilities',1),
(6,'Serious long term or terminally illness',3),
(7,'Male Only',1);
CREATE TABLE Tenant (
`Tenantpk` INT,
`strstatus` VARCHAR(6) CHARACTER SET utf8,
`contactfk` INT
);
INSERT INTO Tenant VALUES
(1,'Active',1),
(2,'Active',2),
(3,'Active',3);
CREATE TABLE IF NOT EXISTS Tenancy (
`Tenancypk` INT,
`address` VARCHAR(25) CHARACTER SET utf8,
`DtePlannedarrival` DATETIME,
`dteRealarrival` VARCHAR(19) CHARACTER SET utf8,
`dtePlannedDepartureDate` VARCHAR(19) CHARACTER SET utf8,
`dteRealDepartureDate` VARCHAR(19) CHARACTER SET utf8,
`tenantfk` INT
);
INSERT INTO Tenancy VALUES
(1,'433 york road en57 7wj','2019-08-01 00:00:00','2019-08-01 00:00:00','2019-08-16 00:00:00','NULL',1),
(2,'656 south street hg70 9qb','2019-08-01 00:00:00','2019-08-02 00:00:00','2019-08-16 00:00:00','NULL',2),
(3,'6 mill road n9 6fg','2019-08-17 00:00:00','NULL','NULL','NULL',1),
(4,'85 kings road nw2 1re','2019-08-17 00:00:00','NULL','NULL','NULL',2),
(5,'9 grange road rg24 5aa','2019-08-17 00:00:00','NULL','NULL','NULL',3),
(6,'7 fake street rg15 5aa','2019-07-01 00:00:00','2019-07-02 00:00:00','2019-07-30 00:00:00','2019-07-31 00:00:00',1);
Select Journeys.Journeypk,
Journeys.mempickuppoint,
Journeys.memdropoffpoint,
Journeys.dteestimatedarrival,
Journeys.strreceivedby,
contacts.name,
tenant.strstatus,
tenancy.dtePlannedDepartureDate,
contacts.strreference,
V1.strflag,
V2.strflag,
V3.strflag,
V4.strflag,
V5.strflag,
V6.strflag
from journeys
left join Journeypassengers on Journeypassengers.journeyfk = journeys.journeypk
left join contacts on contacts.contactpk = Journeypassengers.contactfk
left join tenant on tenant.contactfk = contacts.contactpk
left join tenancy on tenancy.tenantfk = tenant.tenantpk and tenancy.strstatus = 'Active'
left join Vulnerbilityflags as v1 on V1.contactfk = contacts.contactpk and v1.strflag = 'DO NOT VISIT ALONE'
left join Vulnerbilityflags as v2 on v2.contactfk = contacts.contactpk and v2.strflag = 'Female Only'
left join Vulnerbilityflags as v3 on v3.contactfk = contacts.contactpk and v3.strflag = 'Learning Disabilities'
left join Vulnerbilityflags as v4 on v4.contactfk = contacts.contactpk and v4.strflag = 'Male Only'
left join Vulnerbilityflags as v5 on v5.contactfk = contacts.contactpk and v5.strflag = 'Physical Disabilities'
left join Vulnerbilityflags as v6 on v6.contactfk = contacts.contactpk and v6.strflag = 'Serious long term or terminally illness'
输出是这样的
JourneyPK memPickUpPoint memDropOffPoint dteEstimatedArrival strReceivedBy SU1 strStatus dtePlannedDepartureDate SU1 Nass SU1 V SU1 V1 SU1 V2 SU1 V3 SU1 V4 SU1 V5 SU2 strStatus dtePlannedDepartureDate SU2 NASS SU2 V SU2 V1 SU2 V2 SU2 V3 SU2 V4 SU2 V5 SU3 strStatus dtePlannedDepartureDate SU3 NASS SU3 V SU3 V1 SU3 V2 SU3 V3 SU3 V4 SU3 V5
1 london berlin 17/08/2019 jay sam active 16/08/2019 12412 Learning Disabilities Male Only Physical Disabilities mark active 16/08/2019 7854674 Female Only chloe active 568345 Male Only Serious long term or terminally illness
使用 pivot 和 unpivot 语句。选择看起来像这样：
SELECT *
FROM (
SELECT Journeypk,
mempickuppoint,
memdropoffpoint,
dteestimatedarrival,
strreceivedby,
det,
CAST(rn AS NVARCHAR(3)) + '_' + COL AS rr
FROM (
SELECT Journeys.Journeypk,
Journeys.mempickuppoint,
Journeys.memdropoffpoint,
Journeys.dteestimatedarrival,
Journeys.strreceivedby,
CAST(contacts.name AS NVARCHAR(100)) NAME,
CAST(tenant.strstatus AS NVARCHAR(100)) strstatus,
CAST(tenancy.dtePlannedDepartureDate AS NVARCHAR(100)) dtePlannedDepartureDate,
CAST(contacts.strreference AS NVARCHAR(100)) strreference,
CAST(Vulnerbilityflags.strflag AS NVARCHAR(100)) strflag,
ROW_NUMBER() OVER (ORDER BY journeys.Journeypk) AS rn
FROM journeys
LEFT JOIN Journeypassengers
ON Journeypassengers.journeyfk = journeys.journeypk
LEFT JOIN contacts
ON contacts.contactpk = Journeypassengers.contactfk
LEFT JOIN tenant
ON tenant.contactfk = contacts.contactpk
LEFT JOIN tenancy
ON tenancy.tenantfk = tenant.tenantpk
LEFT JOIN Vulnerbilityflags
ON Vulnerbilityflags.contactfk = contacts.contactpk
) AS t
UNPIVOT(
DET FOR COL IN (NAME, strstatus, dtePlannedDepartureDate, strreference, strflag)
) AS h
) AS ttt
PIVOT(
MAX(det) FOR rr IN ([1_dtePlannedDepartureDate], [1_NAME], [1_strflag], [1_strreference], [1_strstatus],
[2_dtePlannedDepartureDate], [2_NAME], [2_strflag], [2_strreference], [2_strstatus],
[3_dtePlannedDepartureDate], [3_NAME], [3_strflag], [3_strreference], [3_strstatus],
[4_dtePlannedDepartureDate], [4_NAME], [4_strflag], [4_strreference], [4_strstatus],
[5_dtePlannedDepartureDate], [5_NAME], [5_strflag], [5_strreference], [5_strstatus],
[6_dtePlannedDepartureDate], [6_NAME], [6_strflag], [6_strreference], [6_strstatus])
) AS pvt
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句