Combining multiple rows of data into one row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Combining multiple rows of data into one row

Author

Topic

sql1411
Starting Member

2 Posts

Posted - 2012-09-20 : 15:53:50

Below is my given scenario and the sample queries.Please let me know if what I'm trying to do here is possible with TSQL.

Thanks in advance.

USE [db_test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON

--Create SourceTable
GO

CREATE TABLE FlightInfo_source (
[DepartureDate] [datetime] NOT NULL,
[FlightNumber] [int] NOT NULL,
[ScheduledTail] [nvarchar](6) NULL,
[ActualDateOfDeparture] [datetime] NULL,
[LastModifiedDateTime] [datetime]NULL,
[Origin][nvarchar](4)NULL,
[Destination][nvarchar](4)NULL,
[TakeOutTime] [nvarchar](4) NULL,
[TakeOffTime] [nvarchar](4) NULL,
[LandOnTime] [nvarchar](4) NULL,
[TakeInTime] [nvarchar](4) NULL,
[CancelledFlightIndicator][char](1)NULL,
[GroundTurnBackIndicator] [char](1) NULL,
[AirTurnBackIndicator][char](1)NULL
) ON [PRIMARY]

GO

--Insert some data into Source table
GO
INSERT INTO dbo.FlightInfo_source
(DepartureDate,FlightNumber,ScheduledTail,ActualDateOfDeparture,LastModifiedDateTime,Origin,Destination,TakeOutTime,TakeOffTime,LandOnTime,TakeInTime,CancelledFlightIndicator,GroundTurnBackIndicator,AirTurnBackIndicator)

SELECT '2012-09-06 00:00:00.000',631,8933,'2012-09-07 00:00:00.000','2012-09-07 13:21:00.000','MSN','DEN',NULL,NULL,NULL,NULL,'Y',NULL,NULL
UNION ALL
SELECT '2012-09-06 00:00:00.000',631, 933, '2012-09-06 00:00:00.000','2012-09-07 13:01:00.000','MSN','MSN',1421,NULL,NULL,1434,NULL,'Y',NULL
UNION ALL
SELECT '2012-09-06 00:00:00.000',631, 933, '2012-09-06 00:00:00.000','2012-09-07 13:01:00.000','MSN','MSN',1513,NULL,NULL,1434,NULL,'Y',NULL
UNION ALL
SELECT '2012-09-06 00:00:00.000',631, 933, '2012-09-06 00:00:00.000','2012-09-07 13:10:00.000','MSN','MSN',1400,1430,1450,1510,NULL,NULL,'Y'
GO
-- Select from the source table
SELECT * FROM dbo.FlightInfo_source

--Create Destination Table
GO

CREATE TABLE FlightInfo_Destination (
[DepartureDate] [datetime] NOT NULL,
[FlightNumber] [int] NOT NULL,
[ScheduledTail] [nvarchar](6) NULL,
[ActualTail] [nvarchar](6) NULL,
[ActualDateOfDeparture] [datetime] NULL,
[LastModifiedDatetime] [datetime] NULL,
[Origin][nvarchar](4)NULL,
[Destination][nvarchar](4)NULL,
[CancelledFlightIndicator][char](1)NULL,
--GroundTurnBackColumns
[GroundTurnBackIndicator] [char](1) NULL,
--for first GroundTurnBack
[GroundTurnBackTail1][nvarchar](6) NULL,
[GroundTurnBackDateOut1] [datetime] NULL,
[GroundTurnBackTimeOut1] [nvarchar] (4) NULL,
[GroundTurnBackDateIn1] [datetime] NULL,
[GroundTurnBackTimeIn1] [nvarchar](4) NULL,
--for second GroundTurnBack
[GroundTurnBackTail2][nvarchar](6) NULL,
[GroundTurnBackDateOut2] [datetime] NULL,
[GroundTurnBackTimeOut2] [nvarchar] (4) NULL,
[GroundTurnBackDateIn2] [datetime] NULL,
[GroundTurnBackTimeIn2] [nvarchar](4) NULL,
--AirturnBackColumns
[AirTurnBackIndicator][char](1)NULL,
--for first AirTrunBack
[AirTurnBackTail1][nvarchar](6) NULL,
[AirTurnBackDateOut1][datetime]NULL,
[AirTurnBackTimeOut1][nvarchar] (4) NULL,
[AirTurnBackDateOff1][datetime]NULL,
[AirTurnBackTimeOff1][nvarchar] (4) NULL,
[AirTurnBackDateOn1][datetime]NULL,
[AirTurnBackTimeOn1][nvarchar] (4) NULL,
[AirTurnBackDateIn1][datetime]NULL,
[AirTurnBackTimeIn1][nvarchar] (4) NULL,
--for second AirTurnBack
[AirTurnBackTail2][nvarchar](6) NULL,
[AirTurnBackDateOut2][datetime]NULL,
[AirTurnBackTimeOut2][nvarchar] (4) NULL,
[AirTurnBackDateOff2][datetime]NULL,
[AirTurnBackTimeOff2][nvarchar] (4) NULL,
[AirTurnBackDateOn2][datetime]NULL,
[AirTurnBackTimeOn2][nvarchar] (4) NULL,
[AirTurnBackDateIn2][datetime]NULL,
[AirTurnBackTimeIn2][nvarchar] (4) NULL,
) ON [PRIMARY]

GO

--Insert some data manually into the destination table. This is, however, the desired result that I want to see in this destination table
GO
INSERT INTO dbo.FlightInfo_Destination
(
DepartureDate, FlightNumber, ScheduledTail,ActualTail,ActualDateOfDeparture,LastModifiedDateTime
,CancelledFlightIndicator,GroundTurnBackIndicator
,GroundTurnBackTail1,GroundTurnBackDateOut1,GroundTurnBackTimeOut1,GroundTurnBackDateIn1,GroundTurnBackTimeIn1
,GroundTurnBackTail2,GroundTurnBackDateOut2,GroundTurnBackTimeOut2,GroundTurnBackDateIn2,GroundTurnBackTimeIn2
,AirTurnBackIndicator
,AirTurnBackTail1,AirTurnBackDateOut1,AirTurnBackTimeOut1,AirTurnBackDateOff1,AirTurnBackTimeOff1,AirTurnBackDateOn1,AirTurnBackTimeOn1,AirTurnBackDateIn1,AirTurnBackTimeIn1
,AirTurnBackTail2,AirTurnBackDateOut2,AirTurnBackTimeOut2,AirTurnBackDateOff2,AirTurnBackTimeOff2,AirTurnBackDateOn2,AirTurnBackTimeOn2,AirTurnBackDateIn2,AirTurnBackTimeIn2
)

SELECT '2012-09-06 00:00:00.000',631,933,NULL,'2012-09-06 00:00:00.000','2012-09-07 13:01:00.000'
,'Y','Y'
,933,'2012-09-06 00:00:00.000',1421,'2012-09-06 00:00:00.000',1434
,933,'2012-09-06 00:00:00.000',1513,'2012-09-06 00:00:00.000',1530
,'Y'
,933,'2012-09-06 00:00:00.000',1400,'2012-09-06 00:00:00.000',1430,'2012-09-06 00:00:00.000',1450,'2012-09-06 00:00:00.000',1510
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL

GO

SELECT * FROM dbo.FlightInfo_Destination;

--Now here the logic is that we're taking the most recently modified rows based on "LastModifiedDateTime" column.
--There can be multiple tail numbers for a particular FlightNumber
--However, all other rows of information from our source table should also be captured in our destination table because that particular flight has ben cancelled once, has the GroundTurnBackIndicator twice, and AirturnBackIndicator once.
-- GroundTurnBackIndicator = 'Y'
When Origin = Destination,TakeOutTime IS NOT NULL, TakeOffTime IS NULL, LandOnTime IS NULL,TakeInTime IS NOT NULL

-- AirTurnBackIndicator = 'Y'
WHEN Origin = Destination,TakeOutTime IS NOT NULL, TakeOffTime IS NOT NULL, LandOnTime IS NOT NULL,TakeInTime IS NOT NULL

--Each time it happens for the given flight number on given date, we've to record it in the same row with new set of columns. Right now, I've added just two set of columns as 1 & 2 (for both GroundTurnBack and AirturnBack groups).
--Let me know if I need to clarify further.

Mike Jackson
Starting Member

37 Posts

Posted - 2012-09-21 : 07:47:30

Are there always 4 records per? Or is it variable, if so what is the hightest number?

sql1411
Starting Member

2 Posts

Posted - 2012-09-21 : 11:04:05

quote:
Originally posted by Mike Jackson

Are there always 4 records per? Or is it variable, if so what is the hightest number?

Hi Mike,

The number of records can vary and we need to capture all the information in one single row for each distinct DepartureDate and FlightNumber. The base row would be based on something like "MAX(LastModifiedDateTime)".
Let me know if I need to clarify further.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources