1

I have a table test123 where the data like below

create table test123
(
id varchar(3),
typ varchar(2),
val int
);

Select * from test123 order by id,typ;

id      typ val
aaa     1a  10
aaa     1b  20
aaa     1c  7
aaa     2a  10
bbb     1a  5
bbb     1d  17

Now I want the output like below using ANSI SQL (which can work in Teradata)

id    1a    1b    1c    1d      2a
aaa   10    20    7     NULL    10
bbb   5     NULL  NULL  17      NULL

Now the number of type can change and accordingly there will be more columns for this.

The sql below from the post Accomplish pivot in teradata sql didn't resolve my question

SELECT DISTINCT
id
-- reiteration starts here
,(SELECT SUM(val) -- assuming you have unique types for every id
  FROM (SELECT DISTINCT
    id
    ,val
    ,typ
    FROM test123
    QUALIFY (RANK() OVER(PARTITION BY typ ORDER BY id ASC))=1 -- variable 1
    ) AS type_1
) AS type_2 -- variable 2
-- reiteration ends here
FROM test123

I also did tried the python code given in the link Accomplish pivot in teradata sql but not getting the expected result

import pymysql

db = pymysql.connect(host="localhost", user="koushik", passwd="koushik", db="koushik")

cur = db.cursor()

for i in range(1, 251):
    print(" \
,(SELECT SUM(value) -- assuming you have unique types for every id \
FROM (SELECT DISTINCT \
id \
,val \
,typ \
FROM test123 \
QUALIFY (RANK() OVER(PARTITION BY typ ORDER BY id ASC))=%d -- variable 1 \
) \
) AS type_%d -- variable 2 \
" % (i, i))

db.close()
Community
  • 1
  • 1
Koushik Chandra
  • 1,565
  • 12
  • 37
  • 73
  • Possible duplicate of [Accomplish pivot in teradata sql](http://stackoverflow.com/questions/22335854/accomplish-pivot-in-teradata-sql) – kennytm Apr 08 '17 at 16:05
  • I am not getting the desired output using the sql from the post you mentioned – Koushik Chandra Apr 08 '17 at 16:21
  • 1
    The linked post shows a veeeeery inefficient answer, but it links to another answer http://stackoverflow.com/a/19036314/2527905, which shows the Standard SQL way using `MAX(CASE)`. If the number of type can change you must to add/remove CASEes accordingly. The only other solution is a Stored Procedure creating this as a Dynamic SQL statement. – dnoeth Apr 09 '17 at 10:32

0 Answers0