Transpose data from wide table to long table

Database
Enthusiast

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

8 REPLIES
Enthusiast

Re: Transpose data from wide table to long table

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' 

Senior Supporter

Re: Transpose data from wide table to long table

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
Enthusiast

Re: Transpose data from wide table to long table

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

Junior Contributor

Re: Transpose data from wide table to long table

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;
Tags (1)
Enthusiast

Re: Transpose data from wide table to long table

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

N/A

Re: Transpose data from wide table to long table

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

Junior Contributor

Re: Transpose data from wide table to long table

There's no simplified way besides Ulrich's CASE, blame that stupid data model :)

Why do you think that 120 columns are too much?  

Re: Transpose data from wide table to long table

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