Author |
Topic |
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2012-10-08 : 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/FareType50 /1 /Regular50 /1 /Regular50 /1 /Regular50 /1 /Senior50 /1 /Senior51 /2 /Regular51 /2 /Regular51 /2 /Regular51 /2 /Regular51 /2 /SeniorI'm not sure the best way to get go about this.Thanks! |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-10-08 : 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.Regularfrom @Ridership rideCROSS 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.Seniorfrom @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,2JimEveryday I learn something that somebody else already knew |
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2012-10-08 : 15:22:04
|
Awesome! Thanks..this is so much simpler than the direction I was heading! |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-10-08 : 15:45:06
|
Glad I could help!JimEveryday I learn something that somebody else already knew |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-08 : 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 BusRegularAS(SELECT BusNum,RouteNum,RegularFare,'Regular' AS FareType,1 AS CntFROM @RideShipUNION ALLSELECT BusNum,RouteNum,RegularFare,FareType,Cnt + 1FROM BusRegularWHERE Cnt + 1 <= RegularFare),BusSeniorAS(SELECT BusNum,RouteNum,SeniorFare,'Senior' AS FareType,1 AS CntFROM @RideShipUNION ALLSELECT BusNum,RouteNum,SeniorFare,FareType,Cnt + 1FROM BusSeniorWHERE Cnt + 1 <= SeniorFare)SELECT BusNum,RouteNum,FareTypeFROM BusRegularUNION ALLSELECT BusNum,RouteNum,FareTypeFROM BusSeniorORDER BY BusNum,RouteNum,FareType ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|