CODEWITHSUNDEEP
sqlsql-serverexcelreference
Compra Dor
Compra Dor

Reputation: 11

Sql Server: Any equivalent of Excel's Hlookup?

I tried to find the answer for this questions but it is very difficult because I don't really know what to write I guess. I already tried "sequential referencing to columns", "indexing columns", and of course "equivalent of HLookup, but nothing seems to work.

Anyway, here comes my question:

I have a table in Excel that has one entry (the identification unique key) in column 1 with 1700 different values (rows). Each of these rows has about 100 columns (that correspond to that key).

I need a procedure to scan this table sequentially and mach the ID entry (in the first column) to the entry in each column and return the value in the corresponding cell.

It would be something like:

For row=1 to N do
    For column = 1 to N do
        Result <- cell[i,j]
    End for
End for

My problem is how to scan the column, because I don't know how to reference the column with some index?

If this is really impossible, do you have any idea about how else I could solve this? For instance copying all the values that are spread across the columns and "transposing" them so they become a single column somehow repeating several copies of the ID_Key on another column?

Thanks for the help!

Excel data:

ID      Date0   DateF   Jun-26  Jun-27  Jun-28  Jun-29  Jun-30  
---------------------------------------------------------------
10006   1926    1953    67.743  71.245  70.139  70.139  70.139  
10014   1926    1961    13.005  12.787  12.63   13.871  14.896  
10022   1926    1960    13.567  13.996  14.326  14.552  14.025  
10030   1926    1966    15.924  17.487  18.771  20.508  20.488  
10049   1926    1932    11.984  12.848  13.34   59.145  61.094  
10057   1926    1968    8.234   8.376   8.448   7.577   8.966  
10065   1926    1929    16.609  15.727  13.849  18.062  -99.99  
10073   1926    1938    14.789  14.912  14.571  15.321  14.926  
10081   1926    1930    1.769   1.83    1.881   1.731   1.703  
10102   1926    1953    14.474  16.604  19.184  21.49   27.895

Upvotes: 0

Views: 1925

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

The first thing you need to do is transform this data into something resembling a database table. I'd suggest you use UNPIVOT to do this.

So, part of your query would be like:

SELECT ID,Date0,DateF,PDate,Value
FROM Table t
  UNPIVOT (Value FOR PDate IN ([Jun-26],[Jun-27],[Jun-28],[Jun-29],[Jun-30])) AS up

Which would give you some rows that look like this:

ID      Date0   DateF   PDate    Value
10006   1926    1953    Jun-26   67.743
10006   1926    1953    Jun-27   71.245
10006   1926    1953    Jun-28   70.139
10006   1926    1953    Jun-29   70.139
10006   1926    1953    Jun-30   70.139
10014   1926    1961    Jun-26   13.005

You could place this in a subquery or CTE. Now, you can build a sensible piece of SQL since you no longer have repeating columns containing the same types of data.

If you need to, you could PIVOT again at the end to turn it back into this Excel style sheet, but I wouldn't recommend it.

Upvotes: 1

alzaimar
alzaimar

Reputation: 4622

The simple answer is: No, you cannot iterate through the columns of a table.

But you can get column information (name, type, size etc.) for each column using a simple query and then construct a string which represents a query

SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo.yourTableName')

Here's a function to create a query over all columns of a certain type. It accepts 3 parameters: table name, some constant value and a datatype ('float' or 'int') Usage: 

select dbo.BuildNumericQuery('MyTable','123','float')

It will create a string which looks something like

'select * from MyTable where Field1=123 or Field2=123 or .... '

And here the code (tadaaa):

create function BuildNumericQuery 
(
  @table varchar(80), 
  @value varchar(10), 
  @datatype varchar(10) 
)
returns varchar(1000)
as
begin
  declare @column varchar(80), @query varchar(1000)
  declare x cursor for
    SELECT name FROM sys.columns 
      WHERE object_id = OBJECT_ID(@table) and (1=
         case when @datatype = 'int' 
               and system_type_id in (48,52,56,127) then 1
              when @datatype = 'float' 
               and system_type_id in (60,62,106,108,122) then 1 
              else 0 end)
  open x
  fetch next from x into @column
  while @@FETCH_STATUS=0 begin
    set @query= case 
      when @query is null then '' 
      else @query+' or ' 
      end + @column+' = '+ @value
    fetch next from x into @column
  end
  close x
  deallocate x
  set @query='select * from ' + @table + 
    case when @query is null then '' else 'where '+@query     
    end
  return @query
end

However, this is not the good solution. Refactoring the table schema is the real deal.

Upvotes: 0

Related Questions