Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
anderson115
Starting Member
1 Post |
 Posted - 2010-03-03 : 21:59:25
|
| I'm pretty new to SQL and I'm trying to convert some SAS code to SQl. In SAS I would use proc SQL, a macro, and some DO LOOPS. But in SQL I really have no idea where to start.Here's what I'm trying to do: Every month I receive a a bunch of tables. For every column in every table I want to run a frequency and count the number of times each value occurs. I want to aggregate the output from each column into a master table.The final output table (FrequencyTable) would include: TABLENAME, COLUMN_NAME, COLUMN_VALUE, COUNTExample the input table (MASTERTABLE) might have columns for Name,order number, productcode, zip, state.I would like to repeat this code for every column in this table and then move onto the next table. insert into FrequencyTableselect [table_name] (literal value or actual table name), [column_name] (literal value or actual column name), [colun_name] (column value) as column_value, count(*) as countfrom MASTERTABLEgroup by [column_name]order by count descI'm envisioning that this code loops once for every unique column name found in the sys.columns table. In each loop the TABLE_NAME and COLUMN_NAME fields take on new values.Is there a mechanism by which I can take the column names from the SYStables and have the SP loop through all unique column values?Many Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-04 : 01:33:26
|
| you need to use cursor or while loop for that------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
|
|
Dynamic Frequency Table Code