Grouping & count codes. 2

[4N6Mstr]

Hello Everybody.

I think is still in time to wish Happy New Year to you all.

Here is my quest.

In an old database, I have four tables for which I have to create a report (Crystal Reports). The table structure is not the same but the common point is each table has more than 250 fields, most of them codes. These codes had their values changed along the time. What I mean is, lets say that BankCode fifteen years ago use to be 001, 002, … 015 then it became A, B, … R. BankApprovalCode use to be P12, P14, P56,… P80 and then Z02, Z14, … Z98. Sometimes codes were abandoned, sometimes codes were added.
For reasons too long (and crazy) to explain here, I need the report to show for a given table, for each of those 200 code fields, each value ever used and the number of times it was used. So using the example above, the desired output is:
BankCode
001 – 200 times
002 – 1500 times
007 – 5 times
A – 800 time
… etc. For each selected code field on that table.

I know that I could create queries to group and count each code and even direct the output to a table and create the report over that table, but still, I’d have to do it somewhere close to 800 times! Ouch!

Is there anything else that I could do to avoid query-frenzy?


Any help is always apreciated!

Thx,


4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there

 

[simian336]

I think I know where you are going but could you show a data example...

Simi

 

[4N6Mstr]

Hi Simian,

It's basically as the small sample I posted. Four Tables, many code fields, and i have to count the number of times each code value was used in each field.

Sorry, I can't post the real data, but believe it's not much different than my fake-samples.

As above

BankCode
Code Number of Times Used
001 – 200 times
002 – 1500 times
007 – 5 times
A – 800 times (and keep doping the same for each value in field BankCode)

Then do the same for
BankApprovalCode
Code Number of Times Used
P12 - 4000 times
P14 - 0 times (zero, yes the code might have never been used)
P18 - 3800 times
...
Z98 - 53000 times

And continue to do the same for each field that is a code. That for each of the four tables.



Any help is always apreciated!

Thx,


4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there

 

[simian336]

I was more after your data structure.

If I have it correctly you have

Field1, Field2, Field3, etc....

001, 007, 001
005, 004, 002
001, 002, 005

You need

001 used 3 times
005 used 2 times
etc...

Is that close?

Simi

 

[4N6Mstr]

Simian,

Sorry i misundertood your request; however, you almost hit it on the head, even without the data structure.

In any case, based on your data, and that the request is the number of times a code appears for each field, the report would show:

Field 1
001 - 2 times
005 - 1 time

Field 2
007 - 1 time
004 - 1 time
002 - 1 time

Field 3
001 - 1 time
002 - 1 time
005 - 1 time

and so on. Is basically a group by / count for each field. I just want to know if there is a way not to repeat the query 800 times.

Any help is always apreciated!

Thx,


4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there

 

[simian336]

I'm probably going to get flamed for this... Both cursors and dynamic SQL are generally discouraged but here goes....


DECLARE @fname VARCHAR(50) -- field name
declare @sqlstr VARCHAR(500) -- hold dynamic sql

DECLARE field_cursor CURSOR
FOR
SELECT name
from sys.columns
where object_id= (
SELECT object_id
from sys.Tables
where name='table1'
)

OPEN field_cursor
FETCH NEXT FROM field_cursor INTO @fname

WHILE @@FETCH_STATUS = 0
BEGIN
set @sqlstr= 'select ' + @fname + ', count(' + @fname +') from dbo.table1 group by ' + @fname
print @sqlstr
exec (@sqlstr);

FETCH NEXT FROM field_cursor INTO @fname
END

CLOSE field_cursor
DEALLOCATE field_cursor


Simi

 

[simian336]

PS I would probably create a temp table insert my results into it, then you could query it for grand totals if neccessary.

Simi

 

[gmmastros]

I would be tempted to write a view that normalizes this data. Then.... the query would be pretty simple to write using the view.

Ex:

Create View YourTableNormalized
AS
Select PrimaryKeyColumn,
       'Field1' As FieldName,
       Field1 As FieldValue
From   YourTable

Union All

Select PrimaryKeyColumn,
       'Field2' As FieldName,
       Field2 As FieldValue
From   YourTable

-- Repeat as necessary

then, to get your query results....

Select FieldName, FieldValue, Count(*) 
From   YourTableNormalized
Group By FieldName, FieldValue
Order By FieldName, FieldValue

I bet that once you have this view, you will probably find a bunch of queries that you can use it for.

To help you create the view...

Select	'Select [!]PrimaryKeyColumnName[/!], ''' + Column_Name + ''' As FieldName, '
        + '[' + Column_Name + '] As FieldValue From [!]TableName[/!]'
        + Char(13) + Char(10)
        + 'Union All'+ Char(13) + Char(10)
From	Information_Schema.Columns 
Where	Table_Name = '[!]Table_Name[/!]'

Copy paste the code above in to a query window. Right click the query window, click "Results To", click "Results To Text". Now, run the query.

In the results window, you will see a bunch of SQL Code. Copy/paste the code from the results window to a new query window. You'll need to clean up a bit. For example, there will be an extra union all at the end that you will need to remove. You'll also need to create the 'Create View VIEWNAME as' at the top, and you'll need to remove any columns you don't want included in the view. But... 99% of the code will be written for you.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[4N6Mstr]

Great! Thank you both. I will test both solutions and decide which one fits best.

Really appreciated.



Any help is always apreciated!

Thx,


4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there