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
– abs786123, Commented Nov 13, 2016 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
– O. Jones, Commented Nov 13, 2016 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
– abs786123, Commented Nov 13, 2016 at 18:40

David דודו Markovitz · Accepted Answer · 2016-11-13 20:37:53Z

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, 2016 at 20:37

answered Nov 13, 2016 at 19:50

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

abs786123 Over a year ago

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?

David דודו Markovitz Over a year ago

@abs786123, yep, use cursor. Just reminding you that you can insert the results of the procedure to a table.

abs786123 Over a year ago

Amazing, just trying to get my head around your logic lol, great stuff

TT. Over a year ago

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.

David דודו Markovitz Over a year ago

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

Community · Accepted Answer · 2020-06-20 09:12:55Z

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, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 13, 2016 at 18:06

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

5 Comments

Vamsi Prabhala Over a year ago

i think the op is looking for a running sum based on the comment.

abs786123 Over a year ago

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 Over a year ago

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.

Gottfried Lesigang Over a year ago

@abs786123, no you absolutely have not been clear about this... See my update

abs786123 Over a year ago

Hi I have updated the question completely to give an insight as to what I am trying to achieve. Thank you

Collectives™ on Stack Overflow

SQL loop for each column in a table

2 Answers 2

5 Comments

UPDATE: Generic approach for different tables

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

UPDATE: Generic approach for different tables

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related