Technical Article

Transforming a vertical name/value to horisontle

,

This Script takes any table wich contains rows with names and valus and converts it into a result set with the names as collumn names and the values as values.

Just set @TableName,@vcName,@vcValue to the desired values.

if exists (select * from sysobjects where name='#Temp')
begin
drop table #Temp
end

declare @vcName varchar(50)
declare @vcValue varchar(50)
declare @TableName varchar(200)
declare @Insert as varchar(500)

set @TableName = 'SystemValues'
set @vcName = 'vcName'
set @vcValue = 'vcValue'

create table #Temp(vcName varchar(250),vcValue varchar(250))

declare Vals cursor  FAST_FORWARD READ_ONLY
for select * from #Temp


set @Insert = 'insert into #Temp select cast(' + @vcName + ' as varchar(250)), cast(' + @vcValue + ' as varchar(250)) from FxPSystemValues'

--print @Insert

exec(@Insert)


declare @Name varchar(250)
declare @Val varchar(250)
declare @Count int

declare @Select varchar(8000)
declare @Join varchar(8000)

declare @Where varchar(8000)

set @Join = ' From #Temp' + char(13) + char(10)


open Vals

FETCH NEXT FROM Vals into @Name,@Val

--print 'HEI'

set @Count = 1

while @@FETCH_STATUS = 0
begin
  --print ltrim(@Name) + '=' + ltrim(@Val) 
  if @Count = 1
  begin
    set @Select = 'Select #Temp.vcValue as ' + @Name 
    set @Where = 'where #Temp.vcName=' + char(39) + @Name + char(39) 
  end
  else
  begin
    set @Select = @Select + ',' + char(13) + char(10) + 'Tab' + cast(@Count as varchar(3)) + '.vcValue as ' + @Name 
    set @Join = @Join + ' Inner join #Temp Tab' + cast(@Count as varchar(3)) + ' on Tab'  + cast(@Count as varchar(3)) + '.vcName=' + char(39) + @Name + char(39) + char(13) + char(10)
  end 
  set @Count = @Count + 1
  FETCH NEXT FROM Vals into @Name,@Val
end


close Vals
deallocate Vals


--print (@Select + @Join + @Where)
exec (@Select + @Join + @Where)

drop table #Temp

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating