Transpose Rows to columns Query for larget data set.

Database
Enthusiast

Transpose Rows to columns Query for larget data set.

Can anyone suggest me how to transpose rows to columns, I have large data set(half million data), read some posts in the forum but found those are used for small data sets. Below is the sample data.

Current data:

LOCLoc_NameGMMGMM_DESCPDIVPDIV_DESCDATESum(UNITS)  
1Albany       123COSMETICS/SHOES               56Shirt201512452  
2Raleigh123COSMETICS/SHOES               56Shirt201512121  
3Housotn123COSMETICS/SHOES               56Shirt201512222  
4Ohio234READY TO WEAR                 65Tie201512261  
          
  Expected Output        
LOCLoc_NameGMMGMM_DESCPDIVPDIV_DESC20151245201512122015122220151226
1Albany       123COSMETICS/SHOES               56Shirt2   
2Raleigh123COSMETICS/SHOES               56Shirt 1  
3Housotn123COSMETICS/SHOES               56Shirt  2 
4Ohio234READY TO WEAR                 65Tie   1
2 REPLIES
Junior Supporter

Re: Transpose Rows to columns Query for larget data set.

Hi,

I am not able to understand your data due for formatting. But, typically you would use below query to transpose your rows to columns:

sel mydate,
max(case rnum when 0 then price end) as p0,
max(case rnum when 1 then price end ) as p1 

max(case rnum when 2 then price end ) as p2 

from mytab

group by 1

i/p:

mydate                   rnum                price

2016-10-28             0                      1

2016-10-28             1                      2

2016-10-28             2                      3

o/p:

mydate                   po                      p1                 p2

2016-10-28             1                         2                  3

Performencewise i think this should be fine.

--Samir Singh

Teradata Employee

Re: Transpose Rows to columns Query for larget data set.

This is the normal method of transposing rows to columns, but is not exactly what the OP has in mind.  It should look like:

LOC..Loc_Name..GMM..GMM_DESC.........PDIV..PDIV_DESC..20151245..20151212..20151222..20151226
..1..Albany....123..COSMETICS/SHOES....56..Shirt.............2
..2..Raleigh...123..COSMETICS/SHOES....56..Shirt.......................1
..3..Housotn...123..COSMETICS/SHOES....56..Shirt.................................2

(I changed the spaces to dots and the font to Courier in order to preserve the layout.)

 

I'm not sure what good this is, but in order to construct a layout like this, you would have to first select all the distinct date values from the table and then write some code to build a dynamic query that uses case operations like the above method, grouping by all the columns.

 

If this a a large table, there are probably a lot of dates.  There is a good chance you could exceed the limit on the number of columns output, if not the limit on the size of the query.  Since you have to write code anyway, you might as well just write a post-processor that reformats the output of the original query.