Cross tab - Pivot

[elvicio]

HI,

I NEED TO DO A ROWCOUNT ON ALL TABLES ON A GIVEN DATABASE. I CAN EASILY DO THIS IN SQL USING A PIVOT TABLE.

CAN SOMEONE HELP?

THIS IS WHAT I HAVE:

LETS SAY THIS IS (TABLE_COUNTS)

TABLENAMES ROWCOUNTS

TABLE1 500

TABLE2 200

TABLE3 800

TABLE4 499

THIS IS WHAT I WANT TO ACCOMPLISH:

LETS SAY THIS IS (TABLE_INPUTDATA)

TABLE1 TABLE2 TABLE3 TABLE4

500 200 800 499

CAN SOMEONE HELP PLEASE. LET ME KNOW IF YOU NEED ADDITIONAL INFORMATION. THANKS.

[elvicio]

I found my own solution. 

[dnoeth]

It's good that you found a solution on your own, but it would be nice if you could share it.

Btw, your question is quite confusing, i still don't know what you actually wanted to do.

Dieter

[elvicio]

Hi Dieter,

I have a few TD tables that I needed to do rowcounts.  e.g.

        count(*) from TableName.

Now, the result of the count is going to a temp table.  Once I get all the counts inserted into the temp table (temptable) then I need to convert those rows to column.  Just like using pivot in SQL server.

The way I accomplished this task was using the following syntax:

Select

Max(Case when t2.tablename in('table1') then rowsnum else null end) table1

,Max(Case when t2.tablename in('table2') then rowsnum else null end) table2

,Max(Case when t2.tablename in('table3') then rowsnum else null end) table3

from

(select tablename as tablename, rowsnum, row_number() over(order by tablename) as RowNr from temptable)

t2

Now, the result of this query get inserted into another permanent table that i can use to compare the counts. 

Now my table read:

databaseName   table1   table2    table3

Raymond               100       200      300

Raymon2               100       200      300

See, the counts match. 

In the event that the count doesn't match, i get notification.  This process it to load a production table from dev.

Thankx,

Raymond V.

[dnoeth]

Hi Raymond,

what if number of tables increases and you finally hit the 2048 columns in a table limit :-)

For me it would be much easier to compare those counts when they are rows in a table.

Dieter

[elvicio]

Thank you Deiter.

I do understand, but I need to keep my counts in a single row for all the tables for history audits.

The ideal solution is if i can convert the following MSSQL server code to Teradata:

[elvicio]

SET NOCOUNT ON;

CREATE TABLE #temp(

 [table1] [int] NULL,

 [table2] [int] NULL,

 [table3] [int] NULL,

 [table4] [int] NULL

) ON [PRIMARY]

INSERT #temp

SELECT

 [table1] ,

 [table2] ,

 [table3] ,

 [table4]

FROM (

SELECT so.name [TableName], si.rows [RowCount]

--   SELECT *

FROM sysobjects so

INNER JOIN sysindexes si on si.id = OBJECT_ID(so.name)

AND SI.INDID <2

WHERE so.xtype = 'U'

) base

PIVOT (MAX([RowCount]) FOR

[TableName] IN (

[table1] ,

 [table2] ,

 [table3] ,

 [table4]

 )) piv

[elvicio]

Sorry, I couldn't put everything in one post.

From here I just insert to Count_History  from #temp

[dnoeth]

There's no place like sysindexes in Teradata which keeps the rowcount.

You need to run multplie SELECTs like

SELECT
   (SELECT COUNT(*) FROM tab1) as cnt1, 
   (SELECT COUNT(*) FROM tab2) as cnt2, 
   ...

Or you extract tha info from the stats, it's not exact, but if stats are mantained regularily it might be good enough.

Dieter