Selectively Pivot - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Selectively Pivot

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2014-03-15 : 15:06:23

I'm trying to create a "times table/schedule" for our bus service would like to pivot all the route/directions time tables separately without having to transpose in Excel. All the data resides in a group of tables. I have no issue getting to each of the route/direction combinations and pivoting one at a time, but I'd like to be able to take all the routes and directions and produce one batch of output files for each route and direction combination. Here's an example of some sample data and some fictitious desired output:

Sample Data Set


declare @table table (route int, stopname varchar(10), direction char(2), tm time, seq int)
insert into @table (route, stopname, direction, tm, seq)
values
 (1, 'DTC', 'SB', '08:00', 1)
,(1, 'COL&PRO', 'SB', '08:05', 2)
,(1, 'COL&HRS', 'SB', '08:10', 3)
,(1, 'COL&HRS', 'NB', '08:15', 1)
,(1, 'COL&PRO', 'NB', '08:20', 2)
,(1, 'DTC', 'NB', '08:25', 3)
,(2, 'CTC', 'WB', '09:15', 1)
,(2, 'SHI&ELI', 'WB', '09:20', 2)
,(2, 'OVE&PRO', 'WB', '09:25', 3)
,(2, 'OVE&PRO', 'EB', '09:30', 1)
,(2, 'SHI&ELI', 'EB', '09:35', 2)
,(2, 'CTC', 'EB', '09:40', 3)
,(2, 'CTC', 'WB', '09:45', 1)
,(2, 'SHI&ELI', 'WB', '09:50', 2)
,(2, 'OVE&PRO', 'WB', '09:55', 3)

Sample Output



route       direction DTC              COL&PRO          COL&HRS
----------- --------- ---------------- ---------------- ----------------
1           SB        08:00            08:05            08:10

(1 row(s) affected)

route       direction COL&HRS          COL&PRO             DTC
----------- --------- ---------------- ---------------- ----------------
1           NB        08:15            08:20            08:25

(1 row(s) affected)


route       direction CTC              SHI&ELI          OVE&PRO
----------- --------- ---------------- ---------------- ----------------
2           WB        09:15            09:20            09:25
2           WB        09:45            09:50            09:55

(2 row(s) affected)

route       direction OVE&PRO          SHI&ELI          CTC
----------- --------- ---------------- ---------------- ----------------
2           EB        09:30            09:35            09:40

(1 row(s) affected)

Any help would be greatly appreciated!

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-03-17 : 20:51:28

Like following?

WITH BASE AS (
SELECT
	route,
	RANK() OVER(PARTITION BY route, stopname, direction ORDER BY tm) busid,
	stopname,
	direction,
	tm,
	seq
FROM @table
)
SELECT
	route,
	direction,
	MAX(DTC) DTC,
	MAX([COL&PRO]) [COL&PRO],
	MAX([COL&HRS]) [COL&HRS]
FROM BASE
PIVOT(
	MAX(tm) FOR stopname IN (DTC, [COL&PRO], [COL&HRS])
) PVT
WHERE route = 1
AND direction = 'SB'
GROUP BY route, direction, busid
ORDER BY busid;

WITH BASE AS (
SELECT
	route,
	RANK() OVER(PARTITION BY route, stopname, direction ORDER BY tm) busid,
	stopname,
	direction,
	tm,
	seq
FROM @table
)
SELECT
	route,
	direction,
	MAX([COL&HRS]) [COL&HRS],
	MAX([COL&PRO]) [COL&PRO],
	MAX(DTC) DTC
FROM BASE
PIVOT(
	MAX(tm) FOR stopname IN (DTC, [COL&PRO], [COL&HRS])
) PVT
WHERE route = 1
AND direction = 'NB'
GROUP BY route, direction, busid
ORDER BY busid;

WITH BASE AS (
SELECT
	route,
	RANK() OVER(PARTITION BY route, stopname, direction ORDER BY tm) busid,
	stopname,
	direction,
	tm,
	seq
FROM @table
)
SELECT
	route,
	direction,
	MAX(CTC) CTC,
	MAX([SHI&ELI]) [SHI&ELI],
	MAX([OVE&PRO]) [OVE&PRO]
FROM BASE
PIVOT(
	MAX(tm) FOR stopname IN (CTC, [SHI&ELI], [OVE&PRO])
) PVT
WHERE route = 2
AND direction = 'WB'
GROUP BY route, direction, busid
ORDER BY busid;

WITH BASE AS (
SELECT
	route,
	RANK() OVER(PARTITION BY route, stopname, direction ORDER BY tm) busid,
	stopname,
	direction,
	tm,
	seq
FROM @table
)
SELECT
	route,
	direction,
	MAX([OVE&PRO]) [OVE&PRO],
	MAX([SHI&ELI]) [SHI&ELI],
	MAX(CTC) CTC
FROM BASE
PIVOT(
	MAX(tm) FOR stopname IN (CTC, [SHI&ELI], [OVE&PRO])
) PVT
WHERE route = 2
AND direction = 'EB'
GROUP BY route, direction, busid
ORDER BY busid;

-------------------------------------
From Japan
Sorry, my English ability is limited.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2014-03-17 : 22:15:02

Hi Nagino,

Each individual CTE that you wrote is exactly what I want to do; however, I wanted to dynamically produce the CTE's or other queries to return results based on the route and direction. I can't pre-define the route, direction or stops...it has to be done dynamically based on the data set. I can already hard-code queries based on the route, direction and stops. The issue is we may have 5 to 10 versions of a bus schedule we need to export and I'm trying to avoid having to do any manual entry to cut down on mistakes.

Thanks!

EDIT: Maybe a better way to describe this is like the "ROW_NUMBER" function. I'm trying to set up a query that will partition based on the route and direction ordered by time. So for example, in my data example, if you sequence through Route 1 NB...once the subsequent row contains a new route and/or direction of travel, then I'd need to set up a new dynamic query to be able to give the correctly pivoted output.

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-03-17 : 22:30:23

1.Get master data.
SELECT DISTINCT route, direction, stopname FROM @table ORDER BY route, direction, seq

2.Construct query strings, like previous query, from master data by frontend application.

3.Execute query strings.

-------------------------------------
From Japan
Sorry, my English ability is limited.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2014-03-17 : 22:41:12

Nagino, that's fine...I'd need to know the routes, directions and stops to place into the query strings. I want to dynamically construct the strings as I will never know what route, direction or stop combinations I'll have. Any thoughts?

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-03-18 : 02:00:03

Why you never know route, direction or stop combinations?
You can get combinations of routes, directions, stops.
Get combinations data (i called master data) by following query.

SELECT DISTINCT route, direction, stopname , seq FROM @table ORDER BY route, direction, seq

Result of the query grouped by route/direction are combinations of route/direction/stopname.
ex. 3 Lines in result, that are "route = 1 and direction = NB", are a combination of route/direction/stopname.

Make combination (grouping of result) are work of frontend application.
And then, construct one query string by one combination, 5..10 query strings by 5..10 combination.
These are work of frontend application, too.

-------------------------------------
From Japan
Sorry, my English ability is limited.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2014-03-18 : 02:54:55

The data I provided was just sample data. It's transactional and will change frequently and combinations are not predictable. I need to be able to loop through the data and re-run select statements each time the route/direction combination changes.

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-03-18 : 03:46:49

So you should construct dynamic sql with datas by frontend application, i said.

-------------------------------------
From Japan
Sorry, my English ability is limited.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources