InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 12:59:30 GMT20http://www.sqlservercentral.com/Forums/Topic899406-338-1.aspx[code="sql"]SELECT U.Name, U.ValueFROM ( SELECT C1Count = COUNT(ASCII(C1)), C2Count = COUNT(ASCII(C2)), C3Count = COUNT(ASCII(C3)), C4Count = COUNT(NULLIF(C4, 0)) FROM #test_data_table ) SUNPIVOT ( Value FOR Name IN (C1Count, C2Count, C3Count, C4Count) ) U;[/code]Fri, 09 Apr 2010 01:10:52 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic899406-338-1.aspxAs a side bar, I'm not sure why you want to avoid dynamic SQL for this... it's not slow when used for batch processess... just for bazillions of single row GUI hits. Since it's not public facing, it's not likely that you need to worry about SQL Injection but preventing it isn't that hard, either.Written correctly, dynamic SQL isn't difficult to troubleshoot, either.Thu, 08 Apr 2010 21:52:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic899406-338-1.aspxThanks again.I had already read most of those excellent articles, but forgotten that I had :hehe:. I've now re-read them fully.A.Thu, 08 Apr 2010 10:18:49 GMTajrendallhttp://www.sqlservercentral.com/Forums/Topic899406-338-1.aspxI think the first method is better then the second with the unions because you are only scanning the source table once. If you have to cross tab/pivot it, check the articles on that in my signature.Thu, 08 Apr 2010 09:50:27 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic899406-338-1.aspxI feared this may be the case.:satisfied:Thanks for the help.Thu, 08 Apr 2010 09:44:43 GMTajrendallhttp://www.sqlservercentral.com/Forums/Topic899406-338-1.aspxI think you have the best way to do this.Thu, 08 Apr 2010 09:32:49 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic899406-338-1.aspxHello everyone,I have a challenge where I need to count the number of rows that have valid data in them across a number of columns. Valid data in this instance is defined as not being blank for character columns, not being 0 for numeric and being more recent than the start of the century for dates.At the moment I am using something like the second method in the following example:[code="sql"]IF OBJECT_ID('tempdb..#test_data_table','U') IS NOT NULL DROP TABLE #test_data_tableIF OBJECT_ID('tempdb..#test_count_table','U') IS NOT NULL DROP TABLE #test_count_tableSELECT 'Data' C1,'Data' C2,'Data' C3,1 C4 INTO #TEST_DATA_TABLE UNION ALLSELECT 'Data','Data','Data',1 UNION ALLSELECT 'Data','','Data',0 UNION ALLSELECT 'Data','Data','',1 UNION ALLSELECT '','Data','Data',0 UNION ALLSELECT '','','Data',1 UNION ALLSELECT '','Data','',1 UNION ALLSELECT '','','',0--One way (creates a result table)SELECTSUM(CASE WHEN C1!='' THEN 1 ELSE 0 END) C1Count,SUM(CASE WHEN C2!='' THEN 1 ELSE 0 END) C2Count,SUM(CASE WHEN C3!='' THEN 1 ELSE 0 END) C3Count,SUM(CASE WHEN C4!=0 THEN 1 ELSE 0 END) C4CountINTO #test_count_tableFROM #test_data_tableSELECT 'C1Count',C1CountFROM #test_count_tableUNIONSELECT 'C2Count',C2CountFROM #test_count_tableUNIONSELECT 'C3Count',C3CountFROM #test_count_tableUNIONSELECT 'C4Count',C4CountFROM #test_count_table--Another way (direct)SELECT 'C1Count',COUNT(C1)FROM #test_data_tableWHERE C1!=''UNIONSELECT 'C2Count',COUNT(C2)FROM #test_data_tableWHERE C2!=''UNIONSELECT 'C3Count',COUNT(C3)FROM #test_data_tableWHERE C3!=''UNIONSELECT 'C4Count',COUNT(C4)FROM #test_data_tableWHERE C4!=0[/code]What I am trying to find out is if there is a more efficient way to do this as while I'm currently testing on some small tables, I will have to run this on some very large ones too (10 million+ rows). I want to get away from using dynamic SQL if possible and would really like a solution where I can make it as set-based as possible.The results need to be in the form presented by either of the above methods (column names as row labels and counts in a column)Any suggestions?Thanks in a advance,AndrewThu, 08 Apr 2010 04:45:04 GMTajrendall