see the exact illustration heredeclare @t1 table(Transport varchar(100),Type varchar(10),Weight int,Service varchar(100))INSERT @t1VALUES ('Road','Car',20,'OwnUse'),('Road','Bus',30,'Management'),('Air','USAirways',50,'USA'),('Air','Lufthansa',25,'Germany')declare @t2 table(Size varchar(10),[Order] int,Ratio decimal(5,1))INSERT @t2VALUES ('Large',1,1.5),('Small',2,0.9)SELECT Transport,[Order],Size,Service,Car,Bus,USAirways,Lufthansa,RatioFROM(SELECT t1.Transport,t2.[Order],t2.Size,t1.Service,t11.Type,t11.Weight,t2.RatioFROM (SELECT Transport,Service FROM @t1) t1CROSS JOIN (SELECT Type,Weight FROM @t1) t11CROSS JOIN @t2 t2)tPIVOT(SUM(Weight) FOR Type IN (Car,Bus,USAirways,Lufthansa))poutput------------------------------------------------------------------------------------------------Transport Order Size Service Car Bus USAirways Lufthansa Ratio------------------------------------------------------------------------------------------------Air 1 Large Germany 20 30 50 25 1.5Air 1 Large USA 20 30 50 25 1.5Air 2 Small Germany 20 30 50 25 0.9Air 2 Small USA 20 30 50 25 0.9Road 1 Large Management 20 30 50 25 1.5Road 1 Large OwnUse 20 30 50 25 1.5Road 2 Small Management 20 30 50 25 0.9Road 2 Small OwnUse 20 30 50 25 0.9
------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs