Need Sql Query

[DSguru2B]

Hi Guys,
I am trying to run a sql query that gives me all the tables and the column names.
I am running a sql query that gets all the code tables. The sql query is

Code: Select all

SELECT Name,TBNAME,COLNO FROM sysibm.syscolumns WHERE RIGHT(TBNAME,2) = 'CD' 
ORDER BY TBNAME

Sample of the result is as follows

Code: Select all

 
Name               TBNAME             COLNO
type_cd            TYPE_CD               0
load_id             TYPE_CD               1
load_tp             TYPE_CD               2
type_desc         TYPE_CD               3

I want a sql query that will group by TBNAME and cat all the column names in the order of the COLNO

The top result needs to be translated into

Code: Select all

Name                                                     TBNAME
type_cd load_id load_tp type_desc           TYPE_CD

This is more like a sql query construct help that i want.
Thanks guys.

[kumar_s]

Are you speaking about PIVOT in DB?

[DSguru2B]

Well, one could say that i want to pivot all the columns corresponding to one table in a single row.

[DSguru2B]

Or, can you suggest me of a way to do it in DS??

[kumar_s]

It can be realised using the transformer stage variables.
Sort based on TBNAME and COLNO. Check for the previous and current row uniqness on the column TBNAME. Based on the same group concatinate the Column name into a single field.
BTW that was actually a Vertical PIVOT.

[DSguru2B]

I will try that. Thanks for that direction. Isnt it possible doing it in the sql query???

[kumar_s]

Let make ETL tool to work more.

[DSguru2B]

Having a problem concatinating the four rows in a single row based on TBNAME. The problem is that #inrows = #outrows in a transformer. Cant seem to cat all the rows in a single row.
Any suggestions guys???

[ratikmishra1]

Take a look at transform "RowProcCompareWithPreviousValue".

You will need two stage vars.
Input shhould be sorted on table name.
Output should be a hash to hold the last record for the table as the transformer stage would be outputting records in the following manner

tab1,col1
tab1,col1,col2
tab1,col1,col2,col3
tab2,co1
tab2,col1,col2
..........

[DSguru2B]

I got it working guys,
Basically i used a bunch of stage variables, stored current and previous values for both column and table names. and then in one stg. variable i did a comparison.
In other words, i basically did this,

Code: Select all

Derivation  =       Stg. Variables
currCol = prevTbl
in.TBNAME = currTbl

and in a third stage variable called 'cond' i did a comparison

Code: Select all

if @INROWNUM =1 then cond:in.Name else if prevTbl = currTbl then cond:',':in.Name else DSLink26.Name

Be sure to initialize the stg. variable with initial value ''

The above logic will provide the last row of each table with all the column names concatenated together in a single row.
Just reverse the sort and pass it through the hash file with TBNAME as key and viola.
Thanks for all your help guys.

[DSguru2B]

Sorry guys,
Actually to get the last row that contains all the columns, i had to pass it through the aggregator, group it by the TBNAME and get the Last row.
That worked just fine.