CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
abs786123
abs786123

Reputation: 609

SQL loop for each column in a table

Say I have a table called:

TableA

The following columns exist in the table are:

Column1, Column2, Column3

what I am trying to accomplish is to see how many records are not null.

to do this I have the following case statement:

sum(Case when Column1 is not null then 1 else 0 end)

What I want is the above case statement for every table that exists from a list provided and to be run for each columns that exists in the table.

So for the above example the case statment will run for Column1, Column2 and Column3 as there are 3 columns in that particular table etc

But I want to specfiy a list of tables to loop through executing the logic above

Upvotes: 2

Views: 13351

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44991

create procedure tab_cols (@tab nvarchar(255))
as
begin

    declare     @col_count  nvarchar(max) = ''
               ,@col        nvarchar(max) = ''

    select      @col_count += case ORDINAL_POSITION when 1 then '' else ',' end + 'count(' +  QUOTENAME(COLUMN_NAME,']') + ') as ' + QUOTENAME(COLUMN_NAME,']')
               ,@col       += case ORDINAL_POSITION when 1 then '' else ',' end + QUOTENAME(COLUMN_NAME,']')
    from        INFORMATION_SCHEMA.COLUMNS
    where       TABLE_NAME = @tab
    order by    ORDINAL_POSITION

    declare     @stmt nvarchar(max) = 'select * from (select ' + @col_count + ' from ' + @tab + ') t unpivot (val for col in (' + @col + ')) u'

    exec sp_executesql @stmt
end

Upvotes: 3

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67331

Wouldn't it be easy as this?

SELECT AccountID
      ,SUM(Total) AS SumTotal
      ,SUM(Profit) AS SumProfit
      ,SUM(Loss) AS SumLoss
FROM tblAccount
GROUP BY AccountID

If I understand this correctly you want to get the sums, but not for all rows in one go but for each accountID separately. This is what GROUP BY is for...

If ever possible try to avoid loops, cursors and other procedural approaches...

UPDATE: Generic approach for different tables

With different tables you will - probably - need exactly the statement I show above, but you'll have to generate it dynamically and use EXEC to execute it. You can go through INFORMATION_SCHEMA.COLUMNS to get the columns names...

But:

  • How should this script know generically, which columns should be summed up? You might head for data_type like 'decimal%' or similar...
  • What about the other columns and their usage in GROUP BY?
  • How would you want to place aliases
  • How do you want to continue with a table of unknown structure?

To be honest: I think, there is no real-generic-one-for-all approach for this...

Upvotes: 0

Related Questions