SQL - Pivot table - uneven rows per column

Hi
I'm trying to format data like this for a report I need to write: (different amount of rows per column) Per City, the list of suburbs must show, and then a list of vehicles that they have in e.g. Austin.

 
     City    Suburbs   Vehicles
  1 Austin  Bastrop   Ford
  2              Hutto     Mazda
  3              Lakeway   Mercedes
  4              Luling  
  5             ShadyHollow 
  6             WellsBranch 
  7             BrushyCreek 
  8             Elgin   
  9             Jollyville  

  10  Dallas    Belmont         BMW
  11                 Eastwood        Ford
  12                 GreenlandHills  
  13                 LakeParkEstates 
  14                 Lochwood    

Open in new window


This is the source data

declare @Test0 table(city nvarchar(10), [state] varchar(30))
   insert into @Test0 values ('Austin',  'Texas')
   insert into @Test0 values ('Dallas',  'Texas')

 declare   @Test1 table (city nvarchar(10), suburb nvarchar(30) ) 
    insert into @Test1 values ('Austin',  'Bastrop')
    insert into @Test1 values ('Austin',  'Hutto')
    insert into @Test1 values ('Austin',  'Lakeway')
    insert into @Test1 values ('Austin',  'Luling')
    insert into @Test1 values ('Austin',  'ShadyHollow')
    insert into @Test1 values ('Austin',  'WellsBranch')
    insert into @Test1 values ('Austin',  'BrushyCreek')
    insert into @Test1 values ('Austin',  'Elgin')
    insert into @Test1 values ('Austin',  'Jollyville')
    insert into @Test1 values ('Dallas',  'Belmont')
    insert into @Test1 values ('Dallas',  'Eastwood' )
    insert into @Test1 values ('Dallas',  'GreenlandHills')
    insert into @Test1 values ('Dallas',  'LakeParkEstates')
    insert into @Test1 values ('Dallas',  'Lochwood')

  declare @Test2 table(city nvarchar(10), vehicle nvarchar(30))
     insert into @Test2 values ('Austin',  'Ford')
     insert into @Test2 values ('Austin',  'Mazda')
     insert into @Test2 values ('Austin',  'Mercedes-Benz')
     insert into @Test2 values ('Dallas',  'BMW')
     insert into @Test2 values ('Dallas',  'Ford' )



 select * from @Test0 t0 join @Test1 t1 on t0.city = t1.city
 join @Test2 t2 on t0.city = t2.city

Open in new window

Where do I start please? Is pivot the answer? Am I even pivoting? Thank you
jxhardingAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
I am afraid, this is best handled in a report.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jxhardingAuthor Commented:
Hi Anthony, do you mean as in a SQL Report?
I am allowed to create temp tables and do calculations on it - is there a way to use temp tables?
0
Anthony PerkinsCommented:
I am allowed to create temp tables and do calculations on it - is there a way to use temp tables?
Yes you can, but it would be extremely painful using some RBAR (Row-By-Agonizing-Row) algorithm involving a CURSOR.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.