April 5, 2012 at 3:50 pm
In my below query, if the data from the 1st column (DepartureTime) up-to-the 2nd last column (ValidFlightPathEndDate) is same then all the data should be in one single row. As such, my last column (Day of the Week) should look like 6,7,1,1,2 or seperated by '\'
In other words, my desired result should look like:
3:00PM 8:34PMDENLGA516003/25/201203/31/20122,3,4
3:00PM 8:34PMDENLGA516103/25/201203/31/20127,5
3:00PM 8:34PMDENLGA516203/30/201204/01/20127
Let me know if someone can help me on this.
Thank you.
---
SELECT DISTINCT RIGHT(FLIGHT_1_DEPARTURE_TIME,7) as DepartureTime
,CASE
WHEN STOPS = 0 THEN RIGHT(FLIGHT_1_ARRIVAL_TIME, 7)
WHEN STOPS = 1 THEN RIGHT(FLIGHT_2_ARRIVAL_TIME, 7)
WHEN STOPS = 2 THEN RIGHT(FLIGHT_3_ARRIVAL_TIME, 7)
END as ArrivalTime
,FLIGHT_PATH_START
,FLIGHT_PATH_END
,FLIGHT_1_FLIGHT_NUMBER as FlightNumber
,STOPS
,CONVERT(varchar(10),VALID_FLIGHT_PATH_START_DATE, 101) as ValidFlightPathStartDate
,CONVERT(varchar(10), VALID_FLIGHT_PATH_END_DATE, 101) as ValidFlightPathEndDate
, CASE
WHEN STOPS = 0 THEN FLIGHT_1_OPERATING_DAY
WHEN STOPS = 1 THEN FLIGHT_2_OPERATING_DAY
WHEN STOPS = 2 THEN FLIGHT_3_OPERATING_DAY
END as 'Day of the Week'
FROM dbo.TBL_Rule_Based_Flight_Path_Data_For_Timetable
WHERE FLIGHT_PATH_START = 'DEN'
AND FLIGHT_PATH_END = 'LGA'
ORDER BY FLIGHT_PATH_START, FLIGHT_PATH_END
----
The result set currently looks like:
3:00PM 8:34PMDENLGA516003/25/201203/31/20122
3:00PM 8:34PMDENLGA516003/25/201203/31/20123
3:00PM 8:34PMDENLGA516003/25/201203/31/20124
3:00PM 8:34PMDENLGA516003/25/201203/31/20126
3:00PM 8:34PMDENLGA516103/25/201203/31/20127
3:00PM 8:34PMDENLGA516103/30/201204/01/20125
3:00PM 8:34PMDENLGA516203/30/201204/01/20127
April 5, 2012 at 4:07 pm
Welcome to sqlservercentral.com!
Please read through the following article on how to provide information to get the question answered.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Your request will involve usage of XML and some GROUP BY clauses. Please provide more information as requested and we shall provide you the query!
April 5, 2012 at 4:16 pm
Sorry, if my post looks weired. I was looking to get the CSV seperated values or seperated by '\' for the 'Day of the Week' column...based on the distinct values for all other columns.
Let me know if I need to clarify my questions further.
Regards.
April 5, 2012 at 4:18 pm
sbj1411 (4/5/2012)
Sorry, if my post looks weired. I was looking to get the CSV seperated values or seperated by '\' for the 'Day of the Week' column...based on the distinct values for all other columns.Let me know if I need to clarify my questions further.
Regards.
It can easily be done without cursors :w00t: But, read the link i provided and provide us more info so that we can work on the query!
April 6, 2012 at 12:57 pm
Create some test data:
IF OBJECT_ID(N'dbo.GroupConcatTestData') > 0
DROP TABLE dbo.GroupConcatTestData ;
GO
CREATE TABLE dbo.GroupConcatTestData
(
DocID INT NOT NULL ,
FieldType NVARCHAR(20) NOT NULL ,
ErrorDetail NVARCHAR(400) NULL
) ;
GO
INSERT INTO dbo.GroupConcatTestData
(
DocID ,
FieldType ,
ErrorDetail
)
SELECT 1 ,
'Sale Date' ,
'Invalid Sale & Date'
UNION ALL
SELECT 1 ,
'DocumentNumber' ,
'DocumentNumber not a number'
UNION ALL
SELECT 1 ,
'Sale Date' ,
'Sale Date Before Open Date'
UNION ALL
SELECT 2 ,
'First Name' ,
'Empty First Name'
UNION ALL
SELECT 3 ,
'Last Name' ,
'Last Name cannot be NULL'
UNION ALL
SELECT 3 ,
'DocumentNumber' ,
'DocumentNumber not a number'
UNION ALL
SELECT 8 ,
'City' ,
'City not found in <State>'
UNION ALL
SELECT 999 ,
'IsAllocated' ,
NULL
UNION ALL
SELECT 3330 ,
'IsUtilized' ,
NULL
UNION ALL
SELECT 3330 ,
'World' ,
'Hello!'
GO
CREATE CLUSTERED INDEX [dbo.GroupConcatTestData.DocID,FieldType] ON dbo.GroupConcatTestData (DocID,FieldType)
GO
Two methods to illustrate:
1. T-SQL:
SELECT DocID,
STUFF(
(SELECT N',' + n
FROM (SELECT DISTINCT
ErrorDetail AS n
FROM dbo.GroupConcatTestData s2
WHERE s2.DocID = s1.DocID
) r
FOR XML PATH(''),
TYPE
).value('.[1]', 'nvarchar(max)'), 1, 1, '') AS [Skills]
FROM dbo.GroupConcatTestData s1
GROUP BY s1.DocID
ORDER BY s1.DocID ;
2. SQLCLR using User-defined aggregate dbo.GROUP_CONCAT found here http://groupconcat.codeplex.com:
SELECT DocID,
dbo.GROUP_CONCAT(DISTINCT ErrorDetail) AS FieldTypeDetail
FROM dbo.GroupConcatTestData
GROUP BY DocID
ORDER BY DocID ;
As others have pointed out, provide DDL and DML for sample data and we'll help you with your specific case.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 6, 2012 at 1:33 pm
Please let me know if this helps in little bit of clarification.
USe MyDb
--Create Table scripts
CREATE TABLE dbo.Flights
(
[FLIGHT_PATH_START] [varchar](3) NULL,
[FLIGHT_PATH_END] [varchar](3) NULL,
[STOPS] [int] NULL,
[FLIGHT_1_OPERATING_DAY] [int] NULL,
[FLIGHT_1_OPERATING_DAY] [int] NULL,
[FLIGHT_3_OPERATING_DAY] [int] NULL,
[VALID_FLIGHT_PATH_END_DATE] [datetime] NULL
) ON [PRIMARY]
--Insert some sampele data
INSERT INTO dbo.Flights(
FLIGHT_PATH_START
,FLIGHT_PATH_END
,STOPS
,FLIGHT_1_OPERATING_DAY
,FLIGHT_1_OPERATING_DAY
,FLIGHT_3_OPERATING_DAY
,VALID_FLIGHT_PATH_END_DATE)
SELECT '6:00AM','DEN','LGA',1,'05/13/2012',1,1,1
UNION ALL
SELECT '6:00AM','DEN','LGA',1,'05/11/2012',2,5,NULL
UNION ALL
SELECT '6:00AM','DEN','LGA',1,'05/11/2012',5,3,NULL
UNION ALL
SELECT '6:00AM','DEN','LGA',1,'05/12/2012',4,4,NULL;
--DataSet query for Report
SELECT FLIGHT_PATH_START
,FLIGHT_PATH_END
,STOPS
,FLIGHT_PATH as RouteMaps
,CONVERT(varchar(10), VALID_FLIGHT_PATH_END_DATE, 101) as ValidFlightPathEndDate
, CASE
WHEN STOPS = 0 THEN FLIGHT_1_OPERATING_DAY
WHEN STOPS = 1 THEN FLIGHT_2_OPERATING_DAY
WHEN STOPS = 2 THEN FLIGHT_3_OPERATING_DAY
END as OperatingDay
FROM dbo.TBL_Rule_Based_Flight_Path_Data_For_Timetable
WHERE FLIGHT_PATH_START = 'DEN'
AND FLIGHT_PATH_END = 'LGA'
----
Here, if the values for all the columns except 'OperatingDay' is same, then I want to see the data in a single row with multiples values in a CSV format for the 'OperatingDay' column.
Thanks.
April 6, 2012 at 1:59 pm
.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 6, 2012 at 3:13 pm
Column missing in the table ( Your insert statments have 8 columns but ur table has 7 ) cna you fix it?
April 6, 2012 at 3:28 pm
Below is the updated INSERT statement
Thanks.
--Insert some sampele data
INSERT INTO dbo.Flights(
FLIGHT_PATH_START
,FLIGHT_PATH_END
,STOPS
,FLIGHT_1_OPERATING_DAY
,FLIGHT_1_OPERATING_DAY
,FLIGHT_3_OPERATING_DAY
,VALID_FLIGHT_PATH_END_DATE)
SELECT 'DEN','LGA',1,'05/13/2012',1,1,1
UNION ALL
SELECT 'DEN','LGA',1,'05/11/2012',2,5,NULL
UNION ALL
SELECT 'DEN','LGA',1,'05/11/2012',5,3,NULL
UNION ALL
SELECT 'DEN','LGA',1,'05/12/2012',4,4,NULL;
April 6, 2012 at 3:47 pm
You're sample data is still causing trouble...but I polished it a little to the point where it will at least run. here is a sample using the SQLCLR Aggregate:
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.Flights')
AND type IN (N'U') )
DROP TABLE dbo.Flights
GO
--Create Table scripts
CREATE TABLE dbo.Flights
(
[FLIGHT_PATH_START] [varchar](3) NULL,
[FLIGHT_PATH_END] [varchar](3) NULL,
[STOPS] [int] NULL,
[FLIGHT_1_OPERATING_DAY] DATETIME NULL,
[FLIGHT_2_OPERATING_DAY] DATETIME NULL,
[FLIGHT_3_OPERATING_DAY] DATETIME NULL,
[VALID_FLIGHT_PATH_END_DATE] [datetime] NULL
)
go
--Insert some sampele data
INSERT INTO dbo.Flights
(
FLIGHT_PATH_START,
FLIGHT_PATH_END,
STOPS,
FLIGHT_1_OPERATING_DAY,
FLIGHT_2_OPERATING_DAY,
FLIGHT_3_OPERATING_DAY,
VALID_FLIGHT_PATH_END_DATE
)
SELECT 'DEN',
'LGA',
1,
'05/13/2012',
1,
1,
1
UNION ALL
SELECT 'DEN',
'LGA',
1,
'05/11/2012',
2,
5,
NULL
UNION ALL
SELECT 'DEN',
'LGA',
1,
'05/11/2012',
5,
3,
NULL
UNION ALL
SELECT 'DEN',
'LGA',
1,
'05/12/2012',
4,
4,
NULL;
SELECT *
FROM dbo.Flights;
--DataSet query for Report
WITH cte
AS (
SELECT FLIGHT_PATH_START,
FLIGHT_PATH_END,
STOPS,
CONVERT(VARCHAR(10), VALID_FLIGHT_PATH_END_DATE, 101) AS ValidFlightPathEndDate,
CASE WHEN STOPS = 0 THEN FLIGHT_1_OPERATING_DAY
WHEN STOPS = 1 THEN FLIGHT_2_OPERATING_DAY
WHEN STOPS = 2 THEN FLIGHT_3_OPERATING_DAY
END AS OperatingDay
FROM dbo.Flights
WHERE FLIGHT_PATH_START = 'DEN'
AND FLIGHT_PATH_END = 'LGA'
)
SELECT FLIGHT_PATH_START,
FLIGHT_PATH_END,
STOPS,
ValidFlightPathEndDate,
GroupConcatTest.dbo.GROUP_CONCAT(CONVERT(VARCHAR(25), OperatingDay, 121))
FROM cte
GROUP BY FLIGHT_PATH_START,
FLIGHT_PATH_END,
STOPS,
ValidFlightPathEndDate;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 6, 2012 at 3:53 pm
Here's the equivalent T-SQL solution:
WITH cte
AS (
SELECT FLIGHT_PATH_START,
FLIGHT_PATH_END,
STOPS,
CONVERT(VARCHAR(10), VALID_FLIGHT_PATH_END_DATE, 101) AS ValidFlightPathEndDate,
CONVERT(VARCHAR(25), CASE WHEN STOPS = 0 THEN FLIGHT_1_OPERATING_DAY
WHEN STOPS = 1 THEN FLIGHT_2_OPERATING_DAY
WHEN STOPS = 2 THEN FLIGHT_3_OPERATING_DAY
END, 121) AS OperatingDay
FROM dbo.Flights
WHERE FLIGHT_PATH_START = 'DEN'
AND FLIGHT_PATH_END = 'LGA'
)
SELECT FLIGHT_PATH_START,
FLIGHT_PATH_END,
STOPS,
ValidFlightPathEndDate,
STUFF(
(SELECT N',' + n
FROM (
SELECT DISTINCT
OperatingDay AS n
FROM cte s2
WHERE (
s1.FLIGHT_PATH_START = s2.FLIGHT_PATH_START
OR (
s1.FLIGHT_PATH_START IS NULL
AND s2.FLIGHT_PATH_START IS NULL
)
)
AND (
s1.FLIGHT_PATH_END = s2.FLIGHT_PATH_END
OR (
s1.FLIGHT_PATH_END IS NULL
AND s2.FLIGHT_PATH_END IS NULL
)
)
AND (
s1.STOPS = s2.STOPS
OR (
s1.STOPS IS NULL
AND s2.STOPS IS NULL
)
)
AND (
s1.ValidFlightPathEndDate = s2.ValidFlightPathEndDate
OR (
s1.ValidFlightPathEndDate IS NULL
AND s2.ValidFlightPathEndDate IS NULL
)
)
) r
FOR XML PATH(''),
TYPE
).value('.[1]', 'nvarchar(max)'), 1, 1, '') AS OperatingDay
FROM cte s1
GROUP BY FLIGHT_PATH_START,
FLIGHT_PATH_END,
STOPS,
ValidFlightPathEndDate;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 10, 2012 at 1:26 pm
Thanks for the help everyone 🙂
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply