SQL PIVOTING

Database
New Member

SQL PIVOTING

I need to convert the rows into columns, the data is scatterd across two tables.

CREATE MULTISET TABLE TAG
( TAG VARCHAR(100), 
  ID VARCHAR(100)
) PRIMARY INDEX (TAG,ID);

INSERT INTO TAG VALUES('L2250','I14299');
INSERT INTO TAG VALUES('L2250','I14300');
INSERT INTO TAG VALUES('L2250','I14301');

CREATE MULTISET TABLE IDS
( ID VARCHAR(100),
  TYPE VARCHAR(100),
  DESCR VARCHAR(100)
) ;

INSERT INTO IDS VALUES ('I14299','Exposure','Bills');
INSERT INTO IDS VALUES ('I14299','Exposure','Certificates');
INSERT INTO IDS VALUES ('I14299','Exposure','NCDS');
INSERT INTO IDS VALUES ('I14300','Currency','GB');
INSERT INTO IDS VALUES ('I14300','Currency','AU');
INSERT INTO IDS VALUES ('I14301','Rate','NOT FIXED');
INSERT INTO IDS VALUES ('I14301','Rate','FIXED');

Intended output is like below, I need to show all the combinations of the ID's i.e 3*2*2 = 12 

 

TAG	       I14299	I14300	I14301
L2250	Bills	        GB	Fixed
L2250	Bills	        GB	NOT FIXED
L2250	Bills	        AU	Fixed
L2250	Bills	        AU	NOT FIXED
L2250	Certificates	GB	Fixed
L2250	Certificates	GB	NOT FIXED
L2250	Certificates	AU	Fixed
L2250	Certificates	AU	NOT FIXED
L2250	NCDS	GB	Fixed
L2250	NCDS	GB	NOT FIXED
L2250	NCDS	AU	Fixed
L2250	NCDS	AU	NOT FIXED
Tags (2)
2 REPLIES
Teradata Employee

Re: SQL PIVOTING

Hi manikambh,

 

This is not really a PIVOT, but you can go like this :

select tg.TAG
     , i1.DESCR as I14299
     , i2.DESCR as I14300
     , i3.DESCR as I14301
  from TAG as tg
  join IDS as i1 on i1.Id = tg.Id and i1.Id = 'I14299'
  join IDS as i2 on i2.Id = 'I14300'
  join IDS as i3 on i3.Id = 'I14301';

 

Teradata Employee

Re: SQL PIVOTING

Waldar is correct in stating that the original request is not a true pivot; however, since the subject is SQL PIVOTING ....

 

Teradata 16.10 and beyond has some really nice pivot functionality. 

 

I've renamed the column named TYPE to be ID_TYPE since TYPE is a reserved word.

 

CREATE MULTISET TABLE TAG
( TAG VARCHAR(100),
ID VARCHAR(100)
) PRIMARY INDEX (TAG,ID);

INSERT INTO TAG VALUES('L2250','I14299');
INSERT INTO TAG VALUES('L2250','I14300');
INSERT INTO TAG VALUES('L2250','I14301');

CREATE MULTISET TABLE IDS
( ID VARCHAR(100),
ID_TYPE VARCHAR(100),
DESCR VARCHAR(100)
) ;

 

INSERT INTO IDS VALUES ('I14299','Exposure','Bills');
INSERT INTO IDS VALUES ('I14299','Exposure','Certificates');
INSERT INTO IDS VALUES ('I14299','Exposure','NCDS');
INSERT INTO IDS VALUES ('I14300','Currency','GB');
INSERT INTO IDS VALUES ('I14300','Currency','AU');
INSERT INTO IDS VALUES ('I14301','Rate','NOT FIXED');
INSERT INTO IDS VALUES ('I14301','Rate','FIXED');

 


select * from
/* generate a derived table to capture only the columns needed
this may not be necesarry if the source table is in the right format;
the columns not listed in the pivot function form an implicit group by.*/

( select t.tag
, i.id
,i.ID_type
, i.descr
from tag t
join IDS i
on t.id = i.id
) dt
pivot

( -- Define the pivot function to be used e.g. SUM, MIN , MAX, etc)
Count(*)
-- Define the Column pairs to be used for the pivot output
-- The first is the value to be used for data selection
-- the second value is the name of the output pivot column

for id_type in ('Exposure' Exposure,
'Currency' Currency,
'Rate' Rate)
) dt2

 

 

TAG   ID     DESCR        Exposure Currency Rate
----- ------ ------------ -------- -------- ----
L2250 I14299 Certificates 1        0        0
L2250 I14300 AU           0        1        0
L2250 I14301 FIXED        0        0        1
L2250 I14300 GB           0        1        0
L2250 I14301 NOT FIXED    0        0        1
L2250 I14299 NCDS         1        0        0
L2250 I14299 Bills        1        0        0

 

 

The key words above that turned on a light for me are "the columns not listed in the pivot function form an implicit group by"

Build a derived table (dt above) that has your group by columns and your pivot columns and then build the pivot function.