Groovy 參考語法

張貼者 於

Object Computing, Inc. – Java News Brief – February 2004

switch (x) { case ‘Mark’: println “got my name" break case 3..7: println ‘got a number in the range 3 to 7 inclusive’ break case [‘Moe’, ‘Larry’, ‘Curly’]: println ‘got a Stooge name’ break case java.util.Date: println ‘got a Date object’ break case ~"\\d{5}": println ‘got a zip code’ break default: println “got unexpected value ${x}" }

TRANSFORM on Access and PIVOT on SQL Server

張貼者 於

in Access

TRANSFORM Sum(accounting2.as_amount) AS SUMASAMOUNT
SELECT accounting1.acc, Month(accounting1.pdate) + Year(accounting1.pdate) AS MONTHEND
FROM accounting_sub INNER JOIN (accounting1 INNER JOIN accounting2 ON accounting1.tr_no = accounting2.tr_no) ON accounting_sub.as_no = accounting2.as_no
WHERE (((accounting_sub.as_no) Like ‘N8′ Or (accounting_sub.as_no)=’N9’))
GROUP BY accounting1.acc, Month(accounting1.pdate) + Year(accounting1.pdate)
ORDER BY accounting1.acc, Month(accounting1.pdate) + Year(accounting1.pdate), accounting_sub.as_no
PIVOT accounting_sub.as_no;

when you want to translate to SQL Server, you can use this method.

create a procedure in SQL Server:

create procedure CrossTab2 (@SQL varchar(1000),
@PivotCol varchar(100),
@Summaries varchar(100),
@GroupBy varchar(100),
@OrderBy VARCHAR(200) = “,
@OtherFields varchar(100) = Null)
AS

set nocount on

declare @Vals varchar(5000);
set @Vals = “;

set @OtherFields = isNull(‘, ‘ + @OtherFields ,")

exec (‘SELECT Distinct convert(varchar(100),’ + @PivotCol + ‘) as TPivot INTO ##Temp FROM (‘ +
@SQL + ‘) A’)

select @Vals = @Vals + ‘, ‘ +
replace(replace(@Summaries,'(‘,'(CASE WHEN ‘ + @PivotCol + ‘="‘ + TPivot +
“‘ THEN ‘),’)[‘, ‘ END) as [‘ + TPivot )
from ##Temp order by TPivot

drop table ##Temp

set nocount off

exec ( ‘select ‘ + @GroupBy + @OtherFields + @Vals + ‘ from (‘ + @SQL + ‘) A GROUP BY ‘ + @GroupBy + @OrderBy )

and use this script

exec CrossTab2 ‘SELECT acc, pdate, accounting_sub.as_no AS AS_NO ,as_amount FROM accounting_sub INNER JOIN (account1 INNER JOIN account2 ON account1.tr_no = account2.tr_no) ON accounting_sub.as_no = account2.as_no’,
‘as_no’,’Sum(as_amount)[]’,’acc, Month(pdate) + Year(pdate)’,’ ORDER BY acc, Month(pdate) + Year(pdate)’

another method is use PIVOT or CASE WHEN or UNION