sql transformation

Database
Enthusiast

sql transformation

i have this below data coming from sql query 

 

date hour count 

2017-08-20 0 8
2017-08-20 1 5
2017-08-20 2 4
2017-08-20 3 3
2017-08-20 4 2
2017-08-20 5 3
2017-08-20 6 4
2017-08-20 7 9
2017-08-20 8 22
2017-08-20 9 67
2017-08-20 10 120
2017-08-20 11 242
2017-08-20 12 388
2017-08-20 13 370
2017-08-20 14 300
2017-08-20 15 244
2017-08-20 16 220
2017-08-20 17 227
2017-08-20 18 228
2017-08-20 19 200
2017-08-20 20 150
2017-08-20 21 73
2017-08-20 22 28
2017-08-20 23 14

2017-08-27 0 8
2017-08-27 1 5
2017-08-27 2 4
2017-08-27 3 3
2017-08-27 4 2
2017-08-27 5 2
2017-08-27 6 3
2017-08-27 7 8
2017-08-27 8 21
2017-08-27 9 62
2017-08-27 10 113
2017-08-27 11 229
2017-08-27 12 368
2017-08-27 13 348
2017-08-27 14 283
2017-08-27 15 235
2017-08-27 16 211
2017-08-27 17 220
2017-08-27 18 221
2017-08-27 19 192
2017-08-27 20 140
2017-08-27 21 67
2017-08-27 22 27
2017-08-27 23 14

 

and i am looking to covert this into the following basically convert into columns, i am looking for this to be dynamic as the date changes add a new column and no of days in the result could be dynamic too.. 

in past i have used case and recursive logic to convert rows to columns but not sure here. how to do it .. 

once i convert it into columns i might want to caclulate average of counts

date hour count date2 hour ccount 

2017-08-20 0 8 2017-08-27 0 8
2017-08-20 1 5 2017-08-27 1 5
2017-08-20 2 4 2017-08-27 2 4
2017-08-20 3 3 2017-08-27 3 3
2017-08-20 4 2 2017-08-27 4 2
2017-08-20 5 3 2017-08-27 5 2
2017-08-20 6 4 2017-08-27 6 3
2017-08-20 7 9 2017-08-27 7 8
2017-08-20 8 22 2017-08-27 8 21
2017-08-20 9 67 2017-08-27 9 62
2017-08-20 10 120 2017-08-27 10 113
2017-08-20 11 242 2017-08-27 11 229
2017-08-20 12 388 2017-08-27 12 368
2017-08-20 13 370 2017-08-27 13 348
2017-08-20 14 300 2017-08-27 14 283
2017-08-20 15 244 2017-08-27 15 235
2017-08-20 16 220 2017-08-27 16 211
2017-08-20 17 227 2017-08-27 17 220
2017-08-20 18 228 2017-08-27 18 221
2017-08-20 19 200 2017-08-27 19 192
2017-08-20 20 150 2017-08-27 20 140
2017-08-20 21 73 2017-08-27 21 67
2017-08-20 22 28 2017-08-27 22 27
2017-08-20 23 14 2017-08-27 23 14

 

the result mentioned above is basically a query from a table by date,hour and count of recrods. 

 

10 REPLIES
Teradata Employee

Re: sql transformation

Have you tried something like:

 

Create Table NewTable as (
select A.dt, A.hr, A.cnt, B.dt, B.hr, B.cnt
from MyTable A, MyTable B
where B.dt = A.dt + 7
and A.hr = B.hr
) with data

Enthusiast

Re: sql transformation

i need it to be dynamic as i dont have no of dates that will be returned by the query , for eg if i query for a month it could be 28,29,30 or 31 days... 

i was reading more about it and it looks like i am trying to pivot rows and teradata doesnt have any inbuilt function to do that , interestingly theres function to unpivot. 

 

Apprentice

Re: sql transformation

I realise this may not help you now. but FYI: PIVOT added in TD 16.0.

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: sql transformation

Yes, this is a pivot, but it's a pretty simple one which you can accomplish by joining the table to itself just once.  And yes, I imagine the number of days between would vary.  That wasn't a solution; it was a question and an example.  To vary the number requires a teeny tiny bit of programming, of course.  You could create a stored procedure that does this or simply create a macro such as:

 

Replace Macro Pivot_Counts (NrDays int) AS (
    Create Table NewTable as (
    select A.dt, A.hr, A.cnt, B.dt, B.hr, B.cnt
    from MyTable A, MyTable B
    where B.dt = A.dt + :NrDays
    and A.hr = B.hr
    ) with data;
)

 

This is just a suggestion for you to experiment with.  You can even add another range of hours (another pivot) with another join, although you may need to add more restrictions on the date range in the predicate ("where-clause").

Enthusiast

Re: sql transformation

certainly helpful information.. we are on 15.0 going to 16.10 some times next year (16.0 is no longer supported i hear) ..

any way that we could get a UDF for pivot and implement it in 15.0 

Apprentice

Re: sql transformation

Sorry. no idea. You'd need to talk (nicely!) to your TD team.

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: sql transformation

I would strongly urge you to try the join approach while you're waiting for an answer on Pivot().  The fact that Pivot() is new does not necessarily mean it is the best solution.  When Pivot() does show up (one way or the other), then you will have at least one way to evaluate its effectiveness for things like this.

Enthusiast

Re: sql transformation

yes i am going to try the join approach and see if i can make it work.. 

my immediate concern about that when i am querying large facts which has billions of rows that might not work out to be the best one (self join) .. 

the table is paritioned so i will be using that to limit the size thats processed in the query 

Teradata Employee

Re: sql transformation

Good! Partitioning should help in this case if the partition is defined on the dates involved.