| Author |
 Topic  |
|
|
flamblaster
Constraint Violating Yak Guru
USA
355 Posts |
 Posted - 10/08/2012 : 14:22:58
|
I'd like to take summary data and split it into multiple rows. I thought of Unpivot, but I'm not sure if that's the right method.
Basically, take the following data:
declare @Ridership table (id int primary key identity not null, BusNum int, RouteNum int, RegularFare int, SeniorFare int)
insert into @Ridership (BusNum, RouteNum, RegularFare, SeniorFare)
values
(50, 1, 3, 2),
(51, 2, 4, 1)
I'd like to to spread out the data in the following manner:
BusNum/RouteNum/FareType
50 /1 /Regular
50 /1 /Regular
50 /1 /Regular
50 /1 /Senior
50 /1 /Senior
51 /2 /Regular
51 /2 /Regular
51 /2 /Regular
51 /2 /Regular
51 /2 /Senior
I'm not sure the best way to get go about this.
Thanks!
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 10/08/2012 : 15:06:34
|
declare @Ridership table (id int primary key identity not null, BusNum int, RouteNum int, RegularFare int, SeniorFare int)
insert into @Ridership (BusNum, RouteNum, RegularFare, SeniorFare)
values
(50, 1, 3, 2),
(51, 2, 4, 1)
select ride.BusNum,ride.RouteNum,a.Regular
from @Ridership ride
CROSS APPLY (Select 'Regular'
from master..spt_values val
where ride.RegularFare >= val.number
and val.type = 'P' and val.number > 0
) a(regular)
UNION all
select ride.BusNum,ride.RouteNum,b.Senior
from @Ridership ride
CROSS APPLY (Select 'Senior'
from master..spt_values val
where ride.SeniorFare >= val.number
and val.type = 'P' and val.number > 0
) b(Senior)
ORDER BY 1,2
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
flamblaster
Constraint Violating Yak Guru
USA
355 Posts |
Posted - 10/08/2012 : 15:22:04
|
| Awesome! Thanks..this is so much simpler than the direction I was heading! |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 10/08/2012 : 15:45:06
|
Glad I could help!
Jim
Everyday I learn something that somebody else already knew |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 10/08/2012 : 19:49:25
|
quote:
Originally posted by flamblaster
Awesome! Thanks..this is so much simpler than the direction I was heading!
if you dont want to reky upon system table master..spt_values you can create a number table yourselves and use it like
;With BusRegular
AS
(
SELECT BusNum,RouteNum,RegularFare,'Regular' AS FareType,1 AS Cnt
FROM @RideShip
UNION ALL
SELECT BusNum,RouteNum,RegularFare,FareType,Cnt + 1
FROM BusRegular
WHERE Cnt + 1 <= RegularFare
),
BusSenior
AS
(
SELECT BusNum,RouteNum,SeniorFare,'Senior' AS FareType,1 AS Cnt
FROM @RideShip
UNION ALL
SELECT BusNum,RouteNum,SeniorFare,FareType,Cnt + 1
FROM BusSenior
WHERE Cnt + 1 <= SeniorFare
)
SELECT BusNum,RouteNum,FareType
FROM BusRegular
UNION ALL
SELECT BusNum,RouteNum,FareType
FROM BusSenior
ORDER BY BusNum,RouteNum,FareType
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Split summary to multi rows
