SQLTeam.com | Weblogs | Forums

I'm currently looking for a dynamic way to convert rows to columns in a specific way in SQL server

sql2008

#1

I'm currently looking for a dynamic way to convert rows to columns in a specific way in SQL server (I was able to do it in excel vba but the excel limitations made me go to SQL).

Abstract : I am making a daily analysis over 10 years considering 1315 stocks, for each stock we have daily returns for a period going from 29/12/2009 to 30/12/2016.

As you can see every 2614 row there's a new stock with the 3 following rows showing text..

Table on SQL

And I would like to obtain this result.. therefore looking for a good insight to help me go through this!

Desired solution draft

I am doing this for a quantitative department in Luxembourg to implement a dynamic model allocation of smart betas. (First time with SQL)

Thank you for your help! feel free to ask any questions if you need any other detail..

R.H.


#2

This is not optimized in any way, so it's most likely very slow:

declare @sql nvarchar(max);
declare @fields nvarchar(max);

set @fields=stuff((select ',['+column0+']'
                     from yourtable
                    where column0 not in ('Code','Mnemonic')
                    group by column0
                    order by case when column0='Name' then 0 else 3 end
                            ,column0
                      for xml path('')
                             ,type
                  ).value('.','nvarchar(max)')
                 ,1
                 ,1
                 ,''
                 )
;

set @sql='
select '+@fields+'
  from (select column0
              ,column1
              ,sum(rn1*rn2) over(order by rn2) as rn
          from (select column0
                      ,column1
                      ,case when column0=''Name'' then 1 else 0 end as rn1
                      ,row_number() over(order by (select null)) as rn2
                  from yourtable
               ) as a
       ) as a
 pivot (max(column1) for column0 in ('+@fields+')) as p
 order by [Name]
'
;

execute sp_executesql @sql;

#3

Thanks a lot ! It really helped me.

Best regards.

R.H.


#4

Hello, would you mind explaining the second part of the code: (Thank you)

sum(rn1*rn2) over(order by rn2) as rn
from (select column0
,column1
,case when column0=''Name'' then 1 else 0 end as rn1
,row_number() over(order by (select null)) as rn2
from yourtable
) as a
) as a
pivot (max(column1) for column0 in ('+@fields+')) as p

because it sends me this error message:
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 68311 which is greater than the allowable maximum row size of 8060.


#5

Seems like it's trying to generate 68311 fields per row.

First part finds unique column0 values.
Second part "lines up" everythins as per your specification.

Now, instead of executing the variable @sql, try showing us what it has generated:

print @sql;

#6

It gives me this when I print:
select ["2006/12/29"],["2007/01/01"],["2007/01/02"],["2007/01/03"],["2007/01/04"],["2007/01/05"],["2007/01/08"],["2007/01/09"],["2007/01/10"],["2007/01/11"],["2007/01/12"],["2007/01/15"],["2007/01/16"],["2007/01/17"],["2007/01/18"],["2007/01/19"],["2007/01/22"],["2007/01/23"],["2007/01/24"],["2007/01/25"],["2007/01/26"],["2007/01/29"],["2007/01/30"],["2007/01/31"],["2007/02/01"],["2007/02/02"],["2007/02/05"],["2007/02/06"],["2007/02/07"],["2007/02/08"],["2007/02/09"],["2007/02/12"],["2007/02/13"],["2007/02/14"],["2007/02/15"],["2007/02/16"],["2007/02/19"],["2007/02/20"],["2007/02/21"],["2007/02/22"],["2007/02/23"],["2007/02/26"],["2007/02/27"],["2007/02/28"],["2007/03/01"],["2007/03/02"],["2007/03/05"],["2007/03/06"],["2007/03/07"],["2007/03/08"],["2007/03/09"],["2007/03/12"],["2007/03/13"],["2007/03/14"],["2007/03/15"],["2007/03/16"],["2007/03/19"],["2007/03/20"],["2007/03/21"],["2007/03/22"],["2007/03/23"],["2007/03/26"],["2007/03/27"],["2007/03/28"],["2007/03/29"],["2007/03/30"],["2007/04/02"],["2007/04/03"],["2007/04/04"],["2007/04/05"],["2007/04/06"],["2007/04/09"],["2007/04/10"],["2007/04/11"],["2007/04/12"],["2007/04/13"],["2007/04/16"],["2007/04/17"],["2007/04/18"],["2007/04/19"],["2007/04/20"],["2007/04/23"],["2007/04/24"],["2007/04/25"],["2007/04/26"],["2007/04/27"],["2007/04/30"],["2007/05/01"],["2007/05/02"],["2007/05/03"],["2007/05/04"],["2007/05/07"],["2007/05/08"],["2007/05/09"],["2007/05/10"],["2007/05/11"],["2007/05/14"],["2007/05/15"],["2007/05/16"],["2007/05/17"],["2007/05/18"],["2007/05/21"],["2007/05/22"],["2007/05/23"],["2007/05/24"],["2007... in a single row


#7

And I also change: over(order by rn2) by over(partition by..)
and deleted "order by [Name]" next to last one row.. because it replied with error messages


#8

Are you planning on displaying this to Users? 3,654 columns seems pretty unworkable to me (daily, from 29-Dec-2006 to 30-Dec-2016 - I make that 3,654 columns)

If you are doing this to then process the columns into something else then it could be done in batches


#9

but how? I will have to exploit this daily data through AI (machine learning).. that's way I am doing it daily


#10

Your code works well with a small sample but with the original case it just replies:

[Name],[2006/12/29],[2007/01/01],[2007/01/02],[2007/01/03],[2007/01/04],[2007/01/05],[2007/01/08]...

Msg 511, Level 16, State 1, Line 2
Cannot create a row of size 36912 which is greater than the allowable maximum row size of 8060.


#11

One year at a time? (Obviously more than one year is fine, up to the max possible column limit)

Maybe shorter column names would help too. "[yyyymmdd]" perhaps ?