All Forums Database
Wildc 6 posts Joined 10/14
13 Oct 2014
Transpose data from wide table to long table

Hi,
 
I have a view where I have listed information about customers in columns and i want to transpose this to a long table like this:
Wide table:
customerid          1            2             3               4             5
12345                  1           0             1               0              0
23456                   1          0              0               0             1
 
Into a long table
 
customerid          id
12345                  1       
12345                  3
23456                  1
23456                  5
 
 
Thanks
 
 

Raja_KT 1246 posts Joined 07/09
13 Oct 2014

try something like this:
select customerid,case when col1<>'0' then '1' else '0' end as id from your_table where col1<>'0' union all
select customerid,case when col2<>'0' then '2' else '0' end as id from your_table where col2<>'0' union all
select customerid,case when col3<>'0' then '3' else '0' end as id from your_table where col3<>'0' union all
select customerid,case when col4<>'0' then '4' else '0' end as id from your_table where col4<>'0' union all
select customerid,case when col5<>'0' then '5' else '0' end as id from your_table where col5<>'0' 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

ulrich 816 posts Joined 09/09
13 Oct 2014

try

select customerid,
       case 
            case when id = 1 and col1 = 1 then 1
            case when id = 2 and col2 = 1 then 2
            case when id = 3 and col3 = 1 then 3
            case when id = 4 and col4 = 1 then 4
            case when id = 5 and col5 = 1 then 5
            else 0
       end as new_col 
from mytable
     cross join
     (select day_of_calendar as id from sys_calendar.calendar where id between 1 and 5)
where new_col > 0

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Wildc 6 posts Joined 10/14
15 Oct 2014

Thanks, but I am searching for an easier command than doing a lot of UNION ALL. My table is quite large so this will be quite an ineffective way of doing it.
 
I know there is an Transpose code in SAS, but i am searching a similar one for Teradata SQL

dnoeth 4628 posts Joined 11/04
15 Oct 2014

Ulrich's code avoids the UNION and is the most efficient solution before TD14.10 which added a TD_UNPIVOT table function:

SELECT customerid, id 
FROM TD_UNPIVOT
 (
   ON (SELECT * FROM vT)
   USING
      VALUE_COLUMNS('val')
      UNPIVOT_COLUMN('id')
      COLUMN_LIST('c1', 'c2', 'c3', 'c4', 'c5')
 ) t
WHERE val <> 0;

 

Dieter

Wildc 6 posts Joined 10/14
15 Oct 2014

Thanks. As i am working with TD14.10 your solution was very efficient, Dieter.
 
 

parimi 1 post Joined 11/11
25 Mar 2015

Hi I Have similar situation... I have a tbl_A data where the value of the column is the columnname of the other tbl_B......
Now i need to pick the column value from tbl_B basing on the columname of tbl_A...
i can't go for case or union all as there are 120 columns...
TBL_A

act_id	msr_id	msr_code
6762	100101	BILL_AMT
6762	101201	BILL_AMT_CLOSE
6762	102301	MARGIN_AMT
6762	103401	PROFIT_AMT
6762	103501	NET_PROFIT_AMT
6762	100601	DISC_AMT
6762	100661	SOLD_AMT

TBL_B

act_id  BILL_AMT  BILL_AMT_CLOSE  MARGIN_AMT  PROFIT_AMT  NET_PROFIT_AMT  DISC_AMT  SOLD_AMT
  6762    345.23          355.23       23.00       23.00           33.00      0.00    356.23

 
Result should be like this:

act_id	msr_id	amt
6762	100101	345.23
6762	100661	356.23

 
i tried like this ... but unable to move from here.

SEL * FROM
  EDW01_D_FIC_DV_BSE_N_01.TBL_A A
JOIN EDW01_D_FIC_DV_BSE_N_01.TBL_B B
ON A.ACT_ID = MEAS.ACT_ID
AND B.MSR_CODE = (SEL COLUMNNAME GROUP BY 1 FROM DBC.TABLES WHERE TABLENAME = 'TBL_A' AND DATABASENAME = 'EDW01_D_FIC_DV_BSE_N_01') 

appreciate faster responce.... Thanks in advance parimi
 

dnoeth 4628 posts Joined 11/04
25 Mar 2015

There's no simplified way besides Ulrich's CASE, blame that stupid data model :)
Why do you think that 120 columns are too much?  

Dieter

clakshminarasu 3 posts Joined 05/15
06 Jun 2016

Dieter - Could you please explain the syntax that you used for TD_UNPIVOT ?
BTW .. I see only these UDFs (TD_SYSFNLIB database - TDAMPCOPY, TD_Unpivot, CalcMatrix) are having TableKind as 'L' or 'C' - what do they mean ?
Thanks !!!
Regards,
Lakshminarasu Chenduri

You must sign in to leave a comment.