SQL loop for each column in a table

Question

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

Hi Sorry, I am at home but its something for work so i don't have the actual data sample available but this is been bugging me but would like to get it sorted tomorrow when I go back to work lol. Basicaly want to loop through each columns I have specified in the query and sum each column as its going along. — abs786123, Nov 13 '16 at 18:06
SQL doesn't allow you to use variables to give table names or column names in queries. You will need to write some software in some other language to generate the query for each table. — O. Jones, Nov 13 '16 at 18:30
I kinda thought that actually, been on dozens of sights hours on end but couldn't find anything just reassuring to know it isn't possible rather than having hope there is a way lol — abs786123, Nov 13 '16 at 18:40

David דודו Markovitz · Accepted Answer · 2016-11-13T20:37:53.737

3

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

edited Nov 13 '16 at 20:37

answered Nov 13 '16 at 19:50

David דודו Markovitz

42,900
6
64
88

Terrific, Thank you it give me what I need but a table at a time running the stored procedure. Is there a way to look over a given a tablename list of catalogue? Not sure if this can done via cursors or set based? – abs786123 Nov 13 '16 at 20:09
@abs786123, yep, use cursor. Just reminding you that you can insert the results of the procedure to a table. – David דודו Markovitz Nov 13 '16 at 20:18
Amazing, just trying to get my head around your logic lol, great stuff – abs786123 Nov 13 '16 at 20:19
I like the solution (1+). Side note: **(1)** `]` is the default quote character, no real need to specify it in calls to `QUOTENAME`. **(2)** You are not taking schema names into account. – TT. Nov 14 '16 at 07:52
@TT. **(1)** I don't like counting on default behaviours. It doesn't mean I'm not falling to this pit once in awhile, but I prefer the code to be clear and safe. **(2)** This is just a POC, feel free to edit it if you want to. – David דודו Markovitz Nov 14 '16 at 08:03

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

0

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...

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 13 '16 at 18:06

Shnugo

66,100
9
53
114

i think the op is looking for a running sum based on the comment. – Vamsi Prabhala Nov 13 '16 at 18:08
It would be but I have a bunch of similar tables with the same structure which I intend to go through. So i was hoping to get the logic for the loop so it can be done in one stored procedure. Sorry if i havent been clear about this part. – abs786123 Nov 13 '16 at 18:09
Nope its not a running sum otherwise I could go down the route of sum()over(etc order by) but I would like a logic I can reuse for all other bunch of tables I need to go through with the same structure and column type. – abs786123 Nov 13 '16 at 18:11
@abs786123, no you **absolutely** have not been clear about this... See my update – Shnugo Nov 13 '16 at 18:15
Hi I have updated the question completely to give an insight as to what I am trying to achieve. Thank you – abs786123 Nov 13 '16 at 18:25

SQL loop for each column in a table

2 Answers2

UPDATE: Generic approach for different tables

Linked