All Forums Data Modeling
lbrec 1 post Joined 03/13
24 Mar 2013
Converting Rows to Columns

We have one business requirment where they want to convert rows to columns. The data we are talking about is 2-3 TB of data. Data looks something in this format.
Table Structure
Date_1                             Unit_Number   Data_ID  Data_Value
2013-01-02 00:00:00      100013           123          671
2013-01-02 00:00:00      100014           131          771 
2013-01-02 00:00:00      100015           281          812
2013-01-02 00:00:00      100016           712          979
2013-01-02 00:00:00      100017           715          719
Pivoted table
Date_1                         RY      XY     HJ    KD    IK      GH    HH  KK   TK  RT ...
2013-01-02 00:00:00  671    771   812  979  719   979 719  980 799 79
2013-01-02 00:10:00  671    771   812  979  719   979 719  980 799 79
and so on
We are pivoting this data using query and creating view using query something like below
select  a.date_1 date_1 a.unit_number system_number,
max(CASE WHEN a.data_id= 123 then a.DATA_Value END) RY,
max(CASE WHEN a.data_id= 281 then a.DATA_Value END) XY,
max(CASE WHEN a.data_id=712 then a.DATA_Value END) HJ,
max(CASE WHEN a.data_id=715 then  a.DATA_Value END) KD,
max(CASE WHEN a.data_id=666 then  a.DATA_Value END) IK,
max(CASE WHEN a.data_id=231 then  a.DATA_Value END) GH,
max(CASE WHEN a.data_id=881 then  a.DATA_Value END) HH,
max(CASE WHEN a.data_id=734 then  a.DATA_Value END) KK,
max(CASE WHEN a.data_id=734 then  a.DATA_Value END) TK,
max(CASE WHEN a.data_id=724 then  a.DATA_Value END) TK,
from FROM table_name group by 1,2
There are about 40 such rows that we are trying to convert into columns. We created indexes, Primary, Secondary and referencial ones to tune this overall query. Also added paritions, We find this conversion works fine for smaller query, however for larger set of data we get high CPU and IO and also often runs into spool error. The data set we are runing on this query are about 3-4 TB. There is another table where we have about 100 rows which we need to convert into one row and again size goes to 4-5 TB
Questions I have
1) Is there better way to convert columns to Rows?
2) What options can be use to reduce CPU, IO and Spool error?
3) Does Hybrid Columanar in TD 14 help in any way to improve performance specially for larger data sets?
4) Any other appproach or design suggested here that will help us?
This design is pushed by our business due to flexibility this design offers and we are runing into performance and we are trying to make this work without impacting CPU, Spool and IO
Any suggestions would help us here, thanks for reading this

KS42982 137 posts Joined 12/12
21 Apr 2013

Try to use WITH RECURSIVE to do the row to column conversion, that would be neat, faster and dynamic. 

dnoeth 4628 posts Joined 11/04
22 Apr 2013

Recursion will blow up spool and i'm not shure if it's possible at all in this case.
The only way to help this query would be a PI on the columns in GROUP BY to get an AMP-local aggregation. Still lots of CPU, but there should be no redistribution and no spool issue anymore.


Adeel Chaudhry 773 posts Joined 04/08
24 Apr 2013

Is it one-time activity? or going to be a continous process?

-- If you are stuck at something .... consider it an opportunity to think anew.

elvicio 12 posts Joined 06/13
03 Jul 2013

can you post the code for recursive?

s@ir@m 35 posts Joined 05/13
23 Feb 2014

Hi all,
i have a table data lik.
but i want display like
how to get ?

Adeel Chaudhry 773 posts Joined 04/08
24 Feb 2014

You can have a look at following links:

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.