All Forums Analytics
tdick 5 posts Joined 12/09
07 Dec 2009
Transposing Data

I am looking to create a simple crosstab query which transposes and sums the below input data into summary output data dynamically. In this case there are 3 dates but I do not want to hard-code the dates into the query. I want the query to identify the dates automatically and place output with one column for each date, named as a date. Harcoding is easily done using sum-case statements but again I do not want to hard code the dates.Input Data:AccountType Date ValueBad 1/31/2009 3Bad 1/31/2009 3Bad 2/28/2009 2Bad 2/28/2009 2Bad 3/31/2009 4Bad 3/31/2009 4Good 1/31/2009 2Good 1/31/2009 2Good 2/28/2009 3Good 2/28/2009 3Good 3/31/2009 4Good 3/31/2009 4Ugly 1/31/2009 3Ugly 1/31/2009 3Ugly 2/28/2009 2Ugly 2/28/2009 2Ugly 3/31/2009 4Ugly 3/31/2009 4Desired Output:AccountType 1-31-09 2-28-09 3-31-09Good 4 6 8Bad 6 4 8Ugly 6 4 8

melina386 8 posts Joined 11/09
08 Dec 2009

Nice Discussion and thanx for posting the information-------------------http://e-datapro.net

tdick 5 posts Joined 12/09
08 Dec 2009

Still have not solved this puzzle - what teradata query SQL works to achieve the above-given output on a dynamic basis? Thanks for any suggestions. . .

Jimm 298 posts Joined 09/07
09 Dec 2009

Teradata (and ANSI SQL) do not directly support pivot/ crosstab queries.This is a presentation level function, so is handled by your presentation tool.If you just get the Account Type/ Date/ Sum(Value), you can use Excel/ BO/ Cognos, etc to do the crosstab dynamically.

tdick 5 posts Joined 12/09
09 Dec 2009

Thanks, agreed that presentation level tool better supports pivot/crosstab. One *can* do this in Tableau, Access, Excel, etc. But if I need to do this directly using Teradata SQL Assistant, and do not want to hard code, question is, what sql is will pivot my data? Seems like several stages of data manipulation are needed. 1) Get list of columns of data to be pivoted (ie a distinct list of dates from column 2 of my source table)2) create temp table with these columns. (ie Desired Output table with no data in it.) 3) Populate the temp table with pivoted data 4) View results in temp table I can do 1, 2 and 4. But how to do 3, ie create a table whose columns are named using a dynamic list created by 1. Kudos for anyone who can do the above in SQL on my sample data (in first post) or point the way within SQL.

Jimm 298 posts Joined 09/07
09 Dec 2009

You need to run the following script in bteq.It creates table Pivot with the required columns and populates it.:-.Foldline 1,2,3,4,5,6,7,8,9,10.Suppress on 1.Export Report File = Runsql.txt-- Generate Create Table Select'Create Table Pivot ( "Account Type" Char(xxx) Not Null' (Title ''), ',"'||Trim("Date")||'" (Smallint) Not Null' (Title '')From "InputData"Order By "Date";Select') Unique Primary Index ("Account Type");' (Title '');-- Generate Populate Table Select 'Select "Account Type" ,' (Title ''), ', Sum(CASE WHEN "Date" = '||Trim("Date")||' Then "Value" Else 0 END) 'From "InputData"Order By "Date";Select' Group By 1 ; '.Export Reset-- Rune the Create and Populate.Run File = Runsql.txt(Untested!)If you want to do it all in SQL Assistant, it can be done but it is a nightmare to generate the SQL!Go on, buy a user interface!

tdick 5 posts Joined 12/09
09 Dec 2009

Thanks, Jimm. I will try the BTEQ solution you provided. But I am really looking for a SQL Assistant SQL code solution. I realize this is complex code. It would be helpful as I have many crosstab reporting needs which I would like to document using SQL code. Not all of my partners have the crosstab presentation utilities which I have, ie tableau, excel, access, etc. Also, to the extent I can follow the code, seems an excellent way to increase one's knowledge of Teradata SQL capabilities. Maybe this exposes an area which might be addressed in a future release of Teradata SQL or SQL Assistant. Thanks for any further followup possible with code which could be used in Teradata SQL Assistant.

Jimm 298 posts Joined 09/07
11 Dec 2009

1. Set up test data---------------------------------------------------Create Multiset Volatile Table "Input Data"("Account Type" Char(12) Not Null, "Date" Date Format 'mm/dd/yyyy' Not Null, "Value" Smallint Not Null)Unique Primary Index ("Account Type","Value")On Commit Preserve Rows;Insert Into "Input Data" Values ('Bad','01/31/2009',3);Insert Into "Input Data" Values ('Bad','01/31/2009',3);Insert Into "Input Data" Values ('Bad','02/28/2009',2);Insert Into "Input Data" Values ('Bad','02/28/2009',2);Insert Into "Input Data" Values ('Bad','03/31/2009',4);Insert Into "Input Data" Values ('Bad','03/31/2009',4);Insert Into "Input Data" Values ('Good','01/31/2009',2);Insert Into "Input Data" Values ('Good','01/31/2009',2);Insert Into "Input Data" Values ('Good','02/28/2009',3);Insert Into "Input Data" Values ('Good','02/28/2009',3);Insert Into "Input Data" Values ('Good','03/31/2009',4);Insert Into "Input Data" Values ('Good','03/31/2009',4);Insert Into "Input Data" Values ('Ugly','01/31/2009',3);Insert Into "Input Data" Values ('Ugly','01/31/2009',3);Insert Into "Input Data" Values ('Ugly','02/28/2009',2);Insert Into "Input Data" Values ('Ugly','02/28/2009',2);Insert Into "Input Data" Values ('Ugly','03/31/2009',4);Insert Into "Input Data" Values ('Ugly','03/31/2009',4);--------------------------------------------------------------------2. Set up the following SQL - I saved it in Pivotbuild.sql via SQLA. This SQL builds a script to compile the results.When you run this, export the results to a file, and make sure that all the results go to a single file (Tools;Options;Export/Import; Write all answer sets to a single file)When it asks for a filename, I gave it pivotrun.sql--------------------------------------------------------------------------- Build Create Table StatementSelect 'Drop Table Pivot;' (Title '');Select'Create Table Pivot ( "Account Type" Char(12) Not Null ' (Title '');Select', "'||Trim("Date")||'" Smallint Not Null ' (Title '')From (Select Distinct "Date" From "Input Data") D1Order By "Date";Select') Unique Primary Index ("Account Type");' (Title '');-- Build Population scriptSelect'Insert Into Pivot Select "Account Type" ' (Title '');Select Distinct ', Sum(CASE WHEN "Date" = '''||Trim("Date")||''' Then "Value" Else 0 END) ' (Title '')From "Input Data"Order By "Date";Select'From "Input Data" Group By 1 ; ' (Title '');Select'Select * From Pivot Order By 1;' (Title '');-----------------------------------------------------------------3. Turn off Export Results and open query built and saved above.This sets up table Pivot and compiles the result into it.Returns: Account Type 01/31/2009 02/28/2009 03/31/2009 Bad 6 4 8 Good 4 6 8 Ugly 6 4 8--------------------------------------------------------------4. Stop messing about and buy a user interface!

PCPaul 4 posts Joined 03/08
15 Dec 2009

Hi, I used Case statements and group by and order by, I got the below result in SQL Assistant itself, if it is fine use case statement. Account_Type 1-31-2009 2-28-2009 3-31-2009 Bad 6 4 8 Good 4 6 8 Ugly 6 4 8Thanks and regards,Chandra Paul.

tdick 5 posts Joined 12/09
16 Dec 2009

Thanks JIMM and PCPaul. PCPaul - am interested to see your case statements and associated code.

Venkatesh_gubba 5 posts Joined 11/10
30 Nov 2010

hi PCPaul, can you post the code using the case statement.

Thanks & Regards,
Venkatesh

Mithafashi 10 posts Joined 11/11
08 Nov 2011

Case statement please

hssahoo 2 posts Joined 12/11
22 Dec 2011

PROC SQL;
CREATE TABLE TEMP AS
SELECT AccountType,
Date1,
SUM(Value) as Value
FROM YOURTABLE
GROUP BY 1,2;
QUIT;

PROC SORT DATA=TEMP;
BY AccountType;
RUN;

PROC TRANSPOSE DATA=TEMP OUT=ABC;
BY AccountType;
ID Date1;
VAR Value;
RUN;

----------------
Himansu

Andrew_S 4 posts Joined 02/07
24 Oct 2012

Thanks for the SAS code.

paul43438 2 posts Joined 09/04
15 Nov 2012

Another solution or rather a slightly different solution.
-- Using Teradata SQL Assistant version 13.10 on a Teradata 13.10 system.
-- Generate Test data
Create Multiset Volatile Table InputData (
AccountType Char(12) Not Null
, Dt Date Format 'mm/dd/yyyy' Not Null
, Value1 Smallint Not Null)
Primary Index (AccountType,Value1)
On Commit Preserve Rows
; Insert Into InputData Values ('Bad','2009-01-31' ,3)
; Insert Into InputData Values ('Bad','2009-01-31',3)
; Insert Into InputData Values ('Bad','2009-02-28',2)
; Insert Into InputData Values ('Bad','2009-02-28',2)
; Insert Into InputData Values ('Bad','2009-03-31',4)
; Insert Into InputData Values ('Bad','2009-03-31',4)
; Insert Into InputData Values ('Good','2009-01-31',2)
; Insert Into InputData Values ('Good','2009-01-31',2)
; Insert Into InputData Values ('Good','2009-02-28',3)
; Insert Into InputData Values ('Good','2009-02-28',3)
; Insert Into InputData Values ('Good','2009-03-31',4)
; Insert Into InputData Values ('Good','2009-03-31',4)
; Insert Into InputData Values ('Ugly','2009-01-31',3)
; Insert Into InputData Values ('Ugly','2009-01-31',3)
; Insert Into InputData Values ('Ugly','2009-02-28',2)
; Insert Into InputData Values ('Ugly','2009-02-28',2)
; Insert Into InputData Values ('Ugly','2009-03-31',4)
; Insert Into InputData Values ('Ugly','2009-03-31',4)
;
-- Query Generator, copy the results back to the editor and then run that query.
select a as "Copy this and " , b as "this to Paste into the editor"
from (
select 'select AccountType '(varchar(100)) as a
, null(varchar(100)) as b
, 0(int) as c
from InputData
group by 1
UNION ALL
Select null(varchar(100))
, ', Sum( Case When dt = ''' || (dt(format'yyyy-mm-dd')(char(10))) || ''' Then Value1 Else 0 End ) as "' || (dt(format'yyyy-mm-dd')(char(10))) || '"'
, row_number() over ( partition by 1 order by dt ) as rn
from ( select distinct Dt from InputData ) as t1
UNION ALL
select 'from inputData group by 1 ;'
, null(varchar(100))
, 10000
from InputData
group by 1 ) as t
order by c
;
-- This is the Query Generated from the "Query Generator"
select AccountType
, Sum( Case When dt = '2009-01-31' Then Value1 Else 0 End ) as "2009-01-31"
, Sum( Case When dt = '2009-02-28' Then Value1 Else 0 End ) as "2009-02-28"
, Sum( Case When dt = '2009-03-31' Then Value1 Else 0 End ) as "2009-03-31"
from inputData group by 1 ;
--Results from the query
AccountType 2009-01-31 2009-02-28 2009-03-31
Ugly             6                  4                8
Bad              6                   4               8
Good            4                   6               8

You must sign in to leave a comment.