ORACLE 系统函数大全SQLSERVER系统函数的异同[转载]

下面是Oracle支持的字符函数和它们的Microsoft SQL Server等价函数。

函数 Oracle Microsoft SQL Server
把字符转换为ASCII :ASCII ASCII
字串连接: CONCAT ————–(expression + expression)
把ASCII转换为字符 CHR, CHAR
返回字符串中的开始字符(左起) INSTR ,—————CHARINDEX
把字符转换为小写 LOWER ———————LOWER
把字符转换为大写 UPPER——————– UPPER
填充字符串的左边 LPAD ——————–N/A
清除开始的空白 LTRIM——————–LTRIM
清除尾部的空白 RTRIM ——————–RTRIM
字符串中的起始模式(pattern) INSTR ——————–PATINDEX
多次重复字符串 RPAD ——————–REPLICATE
字符串的语音表示 SOUNDEX ——————–SOUNDEX
重复空格的字串 RPAD ——————–SPACE
从数字数据转换为字符数据 TO_CHAR ——————–STR
子串 SUBSTR ——————–SUBSTRING
替换字符 REPLACE ——————–STUFF
将字串中的每个词首字母大写 INITCAP ——————–N/A
翻译字符串 TRANSLATE ——————–N/A
字符串长度 LENGTH——————– DATELENGTH or LEN
列表中最大的字符串 GREATEST——————– N/A
列表中最小的字符串 LEAST ——————–N/A
如果为NULL则转换字串 NVL——————– ISNULL Continue reading “ORACLE 系统函数大全SQLSERVER系统函数的异同[转载]”

SQLServer 交叉报表

Cross-Tab Reports
Sometimes it is necessary to rotate results so that columns are presented horizontally and rows are presented vertically. This is known as creating a PivotTable?, creating a cross-tab report, or rotating data.

Assume there is a table Pivot that has one row per quarter. A SELECT of Pivot reports the quarters vertically:

Year      Quarter      Amount
—-      ——-      ——
1990      1           1.1
1990      2           1.2
1990      3           1.3
1990      4           1.4
1991      1           2.1
1991      2           2.2
1991      3           2.3
1991      4           2.4

A report must be produced with a table that contains one row for each year, with the values for each quarter appearing in a separate column, such as:

Year
 Q1
 Q2
 Q3
 Q4
 
1990
 1.1
 1.2
 1.3
 1.4
 
1991
 2.1
 2.2
 2.3
 2.4
 
These are the statements used to create the Pivot table and populate it with the data from the first table:

USE Northwind
GO

CREATE TABLE Pivot
( Year      SMALLINT,
  Quarter   TINYINT,
  Amount      DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO

This is the SELECT statement used to create the rotated results:

SELECT Year,
    SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
    SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
    SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
    SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO

This SELECT statement also handles a table in which there are multiple rows for each quarter. The GROUP BY combines all rows in Pivot for a given year into a single row in the output. When the grouping operation is being performed, the CASE functions in the SUM aggregates are applied in such a way that the Amount values for each quarter are added into the proper column in the result set and 0 is added to the result set columns for the other quarters.

If the results of this SELECT statement are used as input to a spreadsheet, it is easy for the spreadsheet to calculate a total for each year. When the SELECT is used from an application it may be easier to enhance the SELECT statement to calculate the yearly total. For example:

SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
             SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
             SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
             SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
             SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
     FROM Pivot AS P
     GROUP BY P.Year) AS P1
GO

Both GROUP BY with CUBE and GROUP BY with ROLLUP compute the same sort of information as shown in the example, but in a slightly different format.