Help with Insert Statement

General
Enthusiast

Help with Insert Statement

Hi,

I have 2 tables and I'm using Teradata SQLA 16.2 on Windows 7.

I have one table 'Table1' with Row_ID (as PK) + 2 Text fields and some Month/Year values in Row 1.

The other 'Table2' has the Row_ID (as PK) + 2 Text fields and Month/Year but all in Columns 'A'-'D'.

I'm trying to get the Month/Year numeric values from Table2 to transpose onto Table1.

I want to do an Insert that will add the 'Row_ID', the 2 text fields from 'Table2' into 'Table1'

The diagram below may explain better.

Thanks for any suggestions.

Data.PNG

 

 


Accepted Solutions
Junior Contributor

Re: Help with Insert Statement

Transposing rows to columns is called pivoting, before 16.10 you need conditional aggregation (which is still the most flexible way).

I named the "result" column "rslt" because it's a reserved keyword in Teradata:

 

SELECT row_id, field1, field2
  ,Sum(CASE WHEN MonYr = 'Jan-19' THEN rslt end)
  ,Sum(CASE WHEN MonYr = 'Feb-19' THEN rslt end)
  ,Sum(CASE WHEN MonYr = 'Mar-19' THEN rslt end)
  ,Sum(CASE WHEN MonYr = 'Apr-19' THEN rslt end)
  ,Sum(CASE WHEN MonYr = 'Jun-19' THEN rslt end)
  ,Sum(CASE WHEN MonYr = 'Jul-19' THEN rslt end)
FROM table_2
GROUP BY row_id, field1, field2;

But since 16.10 there's a PIVOT syntax, too:

 

 

SELECT *
FROM table_2
PIVOT
 ( Sum(rslt) 
   FOR MonYr
   IN ( 'Jan-19'
       ,'Feb-19'
       ,'Mar-19'
       ,'Apr-19'
       ,'May-19'
       ,'Jun-19'
      )
 ) AS pvt

To insert the result of the Select simply use an

INSERT into table_1

SELECT ...

 

If you downloaded the latest 16.20 VM (with Teradata version 16.20.23.01) there's a new syntax extension which allows to return the values (up to 16) using a Subquery instead of hard-coding it:

 

SELECT *
FROM table_2
PIVOT
 ( Sum(rslt)
   FOR MonYr
   IN ( SELECT MonYr FROM table_2)
 ) AS pvt

But I didn't find a way to define the column order, so it's less usefull in your case.

 

Btw, there's also an UNPIVOT to transpose columns to rows...

 

 

 

 

 

1 ACCEPTED SOLUTION
13 REPLIES
Enthusiast

Re: Help with Insert Statement

Or maybe 'MERGE' suits better.

I found this code to try but I can't seem to apply it to this situation.

 CREATE TABLE t1 (
       a1 INTEGER,
       b1 INTEGER,
       c1 INTEGER)
     PRIMARY INDEX (a1)
     PARTITION BY (c1);

     CREATE TABLE t2 (
       a2 INTEGER,
       b2 INTEGER,
       c2 INTEGER);

     MERGE INTO t1
     USING t2
       ON a1=b2 AND c1=10 AND b1<b2
     WHEN MATCHED THEN
       UPDATE SET b1=b2;

 

Junior Contributor

Re: Help with Insert Statement

Transposing rows to columns is called pivoting, before 16.10 you need conditional aggregation (which is still the most flexible way).

I named the "result" column "rslt" because it's a reserved keyword in Teradata:

 

SELECT row_id, field1, field2
  ,Sum(CASE WHEN MonYr = 'Jan-19' THEN rslt end)
  ,Sum(CASE WHEN MonYr = 'Feb-19' THEN rslt end)
  ,Sum(CASE WHEN MonYr = 'Mar-19' THEN rslt end)
  ,Sum(CASE WHEN MonYr = 'Apr-19' THEN rslt end)
  ,Sum(CASE WHEN MonYr = 'Jun-19' THEN rslt end)
  ,Sum(CASE WHEN MonYr = 'Jul-19' THEN rslt end)
FROM table_2
GROUP BY row_id, field1, field2;

But since 16.10 there's a PIVOT syntax, too:

 

 

SELECT *
FROM table_2
PIVOT
 ( Sum(rslt) 
   FOR MonYr
   IN ( 'Jan-19'
       ,'Feb-19'
       ,'Mar-19'
       ,'Apr-19'
       ,'May-19'
       ,'Jun-19'
      )
 ) AS pvt

To insert the result of the Select simply use an

INSERT into table_1

SELECT ...

 

If you downloaded the latest 16.20 VM (with Teradata version 16.20.23.01) there's a new syntax extension which allows to return the values (up to 16) using a Subquery instead of hard-coding it:

 

SELECT *
FROM table_2
PIVOT
 ( Sum(rslt)
   FOR MonYr
   IN ( SELECT MonYr FROM table_2)
 ) AS pvt

But I didn't find a way to define the column order, so it's less usefull in your case.

 

Btw, there's also an UNPIVOT to transpose columns to rows...

 

 

 

 

 

Enthusiast

Re: Help with Insert Statement

Thanks dnoeth,

I'm trying to avoid the first one as it means a manual addition of the next MonYr as time moves on.

In thius case I'm doing this on a PC that has 16.20 version of Teradata and doesn't use a VM.

I checked the 'about' info & found it had 16.2 for the version of Teradata but 14.20 for the version of database?

Also I noticed that when I typed in PIVOT, it was greyed out and not coloured blue like other function names.

Did UNPIVOT exist before PIVOT?

If so I'm wondering if I could format the data into a table where I only needed to UNPIVOT everything except the MonYr to give me the result I'm after?

Thanks      

Junior Contributor

Re: Help with Insert Statement

There's no 14.20 version of Teradata, you're probably still confuding the database version with the client version :-)

"PC that has 16.20 version of Teradata and doesn't use a VM" seems to indicate it's a Windows PC with a Windows TTU (Teradata Tools and Utilities = client) installed. If you use SQL Assistant (SQLA) and do "Help - About SQL Assistant" you get both a SQLA version and a Database version:

 

tempsnip.png

 

If the client is older than the database it will not know about new features, thus PIVOT might be grayed, but it's still valid syntax (otherwise you get an error message returned by the database when you submit it).

 

UNPIVOT reverts PIVOT, i.e. transpose from your desired result back to table2.

 

And you will always need some manual modification (could be done in a Stored Procedure using Dynamic SQL) because you want to name the columns according to the current month.

Of course the newest syntax (returning the existing months using a subquery) would work, but it returns the columns in a random order, which is probably not what you want (I don't know why it's not possible to define the order, which renders this feature quite useless IMHO)

 

 

 

 

 

 

 

 

 

 

 

Enthusiast

Re: Help with Insert Statement

Thanks dnoeth,

I don't think I have the option of PIVOT for my needs so I've taken this in another direction.

I've created a table that has MonYr values for several years across Row1.

I add the PK 'Row_ID' from the table I want get values to this MonYr table.

I now want to either Update or Insert the Values for the correct MonYr & Row_ID.

What would be good is to change the '201801' value until all values are Inserted.

I know I'm talking about Looping or other so I thought someone might have some helpful suugestions for this issue??

Thanks

 

  Transpose_Issuee.PNG

Enthusiast

Re: Help with Insert Statement

I also tried an Update Statement, same issue, it works OK but I want to avoid hard coding in the MonYr each Month.

UPDATE Table1
FROM  Table2 B
SET 201904 = B.Amount
WHERE Row_ID = B.Row_ID
AND B.MonYr IN (201904)

I need way for the MonYr (201904) to increment one month and Update again??

Any way out ideas or any ideas welcome. 

Enthusiast

Re: Help with Insert Statement

My latest idea is to see if an Array of the MonYr headings would work?

 

UPDATE Table1
FROM  Table2 B
SET MonYr_ARY[1] = B.Amount
WHERE Row_ID = B.Row_ID
AND B.MonYr IN (MonYr_ARY)
Teradata Employee

Re: Help with Insert Statement

Hi OscarBoots,

 

No, it won't work, relationnal database use SQL which is a declarative langage.

The database must know prior to execution what are the tables and columns needed to process a query.

 

AFAIK, the only exception is the subquery in the 16.20 PIVOT syntax.

 

Your use case is exactly a PIVOT.

If you don't want to use the 16.10+ PIVOT syntax - and it's not a problem - just stick to the conditionnal agregation query as also posted by @dnoeth (the sum of cases).

Enthusiast

Re: Help with Insert Statement

Thanks Waldar,

I would like to use PIVOT but it's not available to me.

I agree that dnoeths solution is the most suitable for this version but I was trying to find a way to avoid having to hard code a Month-Year each month.

Appreciate evryones help.

Peter