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?
 
Anthony PerkinsCommented:
I am afraid, this is best handled in a report.
0
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.