Transposing Data

Analytics
N/A

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 Value
Bad 1/31/2009 3
Bad 1/31/2009 3
Bad 2/28/2009 2
Bad 2/28/2009 2
Bad 3/31/2009 4
Bad 3/31/2009 4
Good 1/31/2009 2
Good 1/31/2009 2
Good 2/28/2009 3
Good 2/28/2009 3
Good 3/31/2009 4
Good 3/31/2009 4
Ugly 1/31/2009 3
Ugly 1/31/2009 3
Ugly 2/28/2009 2
Ugly 2/28/2009 2
Ugly 3/31/2009 4
Ugly 3/31/2009 4

Desired Output:
AccountType 1-31-09 2-28-09 3-31-09
Good 4 6 8
Bad 6 4 8
Ugly 6 4 8
14 REPLIES

Re: Transposing Data

Nice Discussion and thanx for posting the information

-------------------
http://e-datapro.net
N/A

Re: Transposing Data

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. . .
N/A

Re: Transposing Data

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.
N/A

Re: Transposing Data


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.

N/A

Re: Transposing Data

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!
N/A

Re: Transposing Data

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.
N/A

Re: Transposing Data

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 Statement

Select '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") D1
Order By "Date"
;
Select
') Unique Primary Index ("Account Type");' (Title '')
;

-- Build Population script
Select
'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!
N/A

Re: Transposing Data

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 8

Thanks and regards,

Chandra Paul.
N/A

Re: Transposing Data

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