Pivoting a table showing values not using aggregate function

• eelkins

  Grasshopper

  Points: 20

  More actions

  July 3, 2006 at 2:43 pm

  #115752

  Hi,

  I am trying to turn a table 90 degrees where each record becomes a column and each column becomes a record. Here is an example

  Before table:

  Name BirthDate Car

  Bob 3/4/1985 Nissan

  Jim 5/4/1943 Cadillac

  Nancy 8/19/1989 Porsche

  After table:

  Name Bob Jim Nancy

  Birthdate 3/4/1985 5/4/1943 8/19/1989

  Car Nissan Cadillac Porsche

  Does anyone have any idea how to do this in a simple select statement? I have already written a sproc utilizing a cursor and lots of variables, but it is very inefficient. I am using SQL 2005 but do not want to turn on the CLR just yet.

  Thanks,

  Eric Elkins

• Pam Brisjar

  SSChampion

  Points: 12094

  More actions

  July 3, 2006 at 3:51 pm

  #647301

  If you're using 2005, you should be able to use PIVOT.

   

  Here's an intro: http://msdn2.microsoft.com/en-us/library/ms177410.aspx

• stax68

  SSChampion

  Points: 11711

  More actions

  July 4, 2006 at 3:50 pm

  #647391

  You will end up with a lot of columns unless your dataset is tiny. And this isn't really like a PIVOT query, since normally a PIVOT leaves at least one row value in rows rather than moving all row values to new columns. you would need to cast all values to the same datatype (a character type most likely) since they have to coexist in the same column.

  I've only ever done this using <paste special - transpose> in Excel. It's not the kind of thing SQL is designed to do. To deal with unknown data values you would almost certainly have to use dynamic SQL. Here's an example:

  --test data-----------------------

  set

  nocount on

  declare

  @before table(_Name varchar(20),BirthDate smalldatetime, Car varchar(20))

  insert

  @before

  select

  'Bob', '3/4/1985', 'Nissan' union all

  select

  'Jim', '5/4/1943', 'Cadillac' union all

  select

  'Nancy', '8/19/1989', null union all

  select

  'Sid', '1/1/2000', 'None' union all

  select

  'Johnny', '8/19/1989', 'Triumph' union all

  select

  'Sue', '8/19/1989', 'Bike' union all

  select

  'Bertram', '8/19/1989', 'Sinclair C5'

  insert

  @before select _name + 'x', BirthDate+1, Car from @before

  insert

  @before select _name + 'y', BirthDate+2, Car from @before

  insert

  @before select _name + 'z', BirthDate+4, Car from @before

  insert

  @before select _name + 'a', BirthDate+8, Car from @before

  insert

  @before select _name + 'b', BirthDate+16, Car from @before

  insert

  @before select _name + 'c', BirthDate+32, Car from @before

  set nocount off

  select

  * from @before

  ------------------------------------

  --

  declare

  @UnionAll varchar(20)

  ,

  @NameRow varchar(max)

  ,

  @BirthdateRow varchar(max)

  ,

  @CarRow varchar(max)

  --

  select

  @UnionAll = ' union all '

  ,

  @NameRow = 'select '

  ,

  @BirthdateRow = 'select '

  ,

  @CarRow = 'select '

  --

  --this has names in column names and in first row. You can remove one.

  select

  @NameRow = @NameRow + 'cast(''' + replace(_name,'''','''''') + ''' as varchar(20)) [' + _name + '],'

  ,

  @BirthdateRow = @BirthdateRow + isnull('''' + cast(Birthdate as varchar(20)) + ''',','null,')

  ,

  @CarRow = @CarRow + isnull('''' + replace(Car,'''','''''') + ''',','null,')

  from

  @before

  order

  by _name asc

  --

  select @NameRow = substring(@NameRow, 1, len(@NameRow)-1)

  ,

  @BirthdateRow = substring(@BirthdateRow, 1, len(@BirthdateRow)-1)

  ,

  @CarRow = substring(@CarRow, 1, len(@CarRow)-1)

  --

  --debug code---------

  select

  len(@NameRow) [len(@NameRow)]

  print

  @NameRow

  print

  @UnionAll

  print

  @BirthdateRow

  print

  @UnionAll

  print

  @CarRow

  ---------------------

  --

  exec

  (@NameRow + @UnionAll + @BirthdateRow + @UnionAll + @CarRow)

  Tim Wilkinson

  "If it doesn't work in practice, you're using the wrong theory"
  - Immanuel Kant