All Forums Database
smplisri 7 posts Joined 02/13
19 Feb 2013
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;

Regards, Kalyan.
19 Feb 2013
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
mohan.mscss 31 posts Joined 04/11
19 Feb 2013

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

19 Feb 2013

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

smplisri 7 posts Joined 02/13
19 Feb 2013

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?

Regards,
Kalyan.

22 Feb 2013

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

Budda123 3 posts Joined 02/13
22 Feb 2013

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.
                                                                                                                                      

 

M.Budda

smplisri 7 posts Joined 02/13
25 Feb 2013

Thanks Sachin.

Regards,
Kalyan.

adam01 1 post Joined 01/14
18 Jan 2014

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.

dnoeth 4628 posts Joined 11/04
19 Jan 2014
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

 

Dieter

You must sign in to leave a comment.