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:
customerid 1 2 3 4 5
12345 1 0 1 0 0
23456 1 0 0 0 1
Into a 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'
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
end as new_col
(select day_of_calendar as id from sys_calendar.calendar where id between 1 and 5)
where new_col > 0
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
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
ON (SELECT * FROM vT)
COLUMN_LIST('c1', 'c2', 'c3', 'c4', 'c5')
WHERE val <> 0;
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...
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
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
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
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 - 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 ?