How to transpose rows to columns without the use of .sql and Bteqs

Database
Enthusiast

How to transpose rows to columns without the use of .sql and Bteqs

Hi,

 

Can anyone provide me a logic to apply transpose on rows to columns without the use of any other files(like .sqls or Bteq)? I need to select this result from different tables and insert it into a table. I have used left outer joins but is there any other easy way to do this?

Input:-

Account table

Acct_num       Acct_type

1234              A

2345              B

Alternate Account table

Acct_num      Acct_type     Alt_id         Alt_id_type

1234              A                  12             C_id

1234              A                  34             D_id

2345              B                  23             D_id

2345              B                  45             E_id

 

Output:-

Acct_num     Acct_type      C_id      D_id          E_id

1234             A                     12        34              ?

2345             B                      ?         23             45

 

* where ? means NULL.

 

My query:-

Select ac.Acct_num, ac.Acct_type,

          al_c.alt_id as C_id,

          al_d.alt_id as D_id,

          al_e.alt_id as E_id

from  Account ac

left outer join (sel * from Alt_tbl where alt_id_type = 'C_id') al_c

on al_c.acct_num = ac.acct_num and

     al_c.acct_type = ac.acct_type

left outer join (sel * from Alt_tbl where alt_id_type = 'D_id') al_d

on al_d.acct_num = ac.acct_num and

     al_d.acct_type = ac.acct_type

left outer join (sel * from Alt_tbl where alt_id_type = 'E_id') al_e

on al_e.acct_num = ac.acct_num and

     al_e.acct_type = ac.acct_type;

9 REPLIES
Enthusiast

Re: How to transpose rows to columns without the use of .sql and Bteqs

SELECT Acct_num , Acct_type,
          MAX(CASE WHEN Alt_id_type = 'C_id' THEN Alt_id END) AS C_id,
          MAX(CASE WHEN Alt_id_type = 'D_id' THEN Alt_id END)  AS D_id,
          MAX(CASE WHEN Alt_id_type = 'E_id' THEN Alt_id END)  AS E_id
FROM Alt_tbl
GROUP BY Acct_num, Acct_type

Enthusiast

Re: How to transpose rows to columns without the use of .sql and Bteqs

Hi Sachin,

I tried execute your query with the below inputs it seems you picked the max value for particular Alt_id_type.

Here for 1234, i have just changed C_id instead of D_id and it seems getting 1 row for the coresponding id, but as per my understanding it could have been come with 2 rows.

Acct_num      Acct_type     Alt_id         Alt_id_type

1234              A                  12             C_id

1234              A                  34             C_id

2345              B                  23             D_id

2345              B                  45             E_id

Please have look and correct if I am wrong!

Regards,

Mohan K

Enthusiast

Re: How to transpose rows to columns without the use of .sql and Bteqs

Mohan,

The sql I provided was based on the premise that there would be unique Alt_id_type for Acct_num, Acct_type combination.

Enthusiast

Re: How to transpose rows to columns without the use of .sql and Bteqs

Yes there will be only one ALT_ID under a ALT_ID_TYPE_CODE for an ACCOUNT..

Thanks, Sachin. But under my requirements I am not allowed to use a group by on over all select. So this is were it gets messy. This is why I raised a post here. So is there any other method like using stored procs etc.,. to get the data like above?

Enthusiast

Re: How to transpose rows to columns without the use of .sql and Bteqs

You can also use windowed aggregate functions, like Rank() Over (Partition Acct_num, Acct_type)

Re: How to transpose rows to columns without the use of .sql and Bteqs

HI All,

I too have same query(Need to Convert rows to cols anyone help me how to resolve it.

Input:  

Product     State          population       

HSI             MP                  12            

FIN             AP                   34            

FDV            UP                   23            

HSI             MP                  45

FIN              AP                  60

FDV             UP                  50

sel product,

sum( case when state='AP' then population  else 0 end) as "AP",

sum( case when state='MP' then population  else 0 end) as "MP",

sum( case when state='UP' then population  else 0  end) as "UP"

from WORK_TBLS_LOADS.Ptable  group by product;   

output:

product  AP     MP         UP

FDV    0         0          73

FIN      94       0          0

HSI      0         57        0

Question: I need this query in dynamic way (There is fixed states). Please provide ur input.

 

Enthusiast

Re: How to transpose rows to columns without the use of .sql and Bteqs

Thanks Sachin.

N/A

Re: How to transpose rows to columns without the use of .sql and Bteqs

Hi All,

I am new to Teradata,

Could you please any one help me to solve this.

The table:

WUNO          ID             Passport        Licence

1234        G8799G6                

1234                                                  K73289H3

1234                             F8738E83

1234        B83782J2      U92979H9

1110                             G897H934     F839329J2

1110        H73672H1    J8263G23

1110        A73692L92                         K9839H29

Condtions:

Step 1. ID_NO= If ID<> Blank Then ID Else If  Passport <> Blank Then Passport Else Licence

Step 2. Sort ID_NO in Ascending Order.

Step 3. Concatenate All ID_NO 

Output:

Step 1)  1234    G8799G6       

             1234    K73289H3

             1234    F8738E83

             1234    B83782J2

             1110   G897H934

             1110    H73672H1

             1110    A73692L92

Step 2)

            1234    B83782J2

             1234    F8738E83

             1234    G8799G6       

             1234    K73289H3

             1110   A73692L92

             1110    G897H934

             1110      H73672H1

Step 3) Final Output should be

     1234     B83782J2_F8738E83_G8799G6_K73289H3

     1110     A73692L92_G897H934_H73672H1

Please anyone help me ASAP for the above request.

Senior Apprentice

Re: How to transpose rows to columns without the use of .sql and Bteqs

SELECT
WUNO,
MAX(CASE WHEN rnk = 1 THEN x ELSE '' END)
|| MAX(CASE WHEN rnk = 2 THEN '_' || x ELSE '' END)
|| MAX(CASE WHEN rnk = 3 THEN '_' || x ELSE '' END)
|| MAX(CASE WHEN rnk = 4 THEN '_' || x ELSE '' END)
|| MAX(CASE WHEN rnk = 5 THEN '_' || x ELSE '' END)
FROM
(
SELECT
WUNO,
CASE
WHEN ID <> '' THEN ID
WHEN Passport <> '' THEN Passport
WHEN Licence <> '' THEN Licence
END AS x,
RANK() OVER (PARTITION BY WUNO ORDER BY x) AS rnk
FROM tab
) AS dt
GROUP BY 1