Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Beginner
Sep 10, 2001

Spreading Data across Months on a Single Row


This is a SQL trick for spreading out data across months on a single row. It also works with Sybase TSQL. The 1-ABS(SIGN()) function will evaluate to zero for all months outside of column.
 
SELECT	 A,B,C,FISCAL_YEAR,
may = SUM(T1.number_of_cases * (1 - ABS(SIGN(T1.fiscal_month - 1)))),
jun = SUM(T1.number_of_cases * (1 - ABS(SIGN(T1.fiscal_month - 2)))),
jul = SUM(T1.number_of_cases * (1 - ABS(SIGN(T1.fiscal_month - 3)))),
aug = SUM(T1.number_of_cases * (1 - ABS(SIGN(T1.fiscal_month - 4)))),
sep = SUM(T1.number_of_cases * (1 - ABS(SIGN(T1.fiscal_month - 5)))),
oct = SUM(T1.number_of_cases * (1 - ABS(SIGN(T1.fiscal_month - 6)))),
nov = SUM(T1.number_of_cases * (1 - ABS(SIGN(T1.fiscal_month - 7)))),
dec = SUM(T1.number_of_cases * (1 - ABS(SIGN(T1.fiscal_month - 8)))),
jan = SUM(T1.number_of_cases * (1 - ABS(SIGN(T1.fiscal_month - 9)))),
feb = SUM(T1.number_of_cases * (1 - ABS(SIGN(T1.fiscal_month -10)))),
mar = SUM(T1.number_of_cases * (1 - ABS(SIGN(T1.fiscal_month -11)))),
apr = SUM(T1.number_of_cases * (1 - ABS(SIGN(T1.fiscal_month -12))))
FROM	 table_name T1
GROUP BY A,B,C,FISCAL_YEAR
Debasish Chatterjee
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date