时间戳

首页 > 解决方案 > 将 SQL Server T-SQL 转换为 SQL

sql - 将 SQL Server T-SQL 转换为 SQL

问题描述

我一直在尝试使用pivot 从表中获取特定视图的代码。

create table temp
(
    date datetime,
    category varchar(3),
    amount money
)

insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('3/1/2012', 'ABC', 1100.00)


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT date, ' + @cols + ' from 
            (
                select date
                    , amount
                    , category
                from temp
           ) x
            pivot 
            (
                 max(amount)
                for category in (' + @cols + ')
            ) p '


execute(@query)

drop table temp

declare我在语句中收到错误，并且pivotdbviz SQL 指挥官也没有将其识别为关键字

我什至删除@并尝试运行它，我无法成功执行

我正在使用 Oracle Database 11g（oracle JDBC 驱动程序）

标签： sqloraclepivot

解决方案

如果您尝试从 SQLPlus 或 SQLcl 运行此代码，您可以使用下面的代码在 Oracle 中生成相同的信息。

Oracle 19c 及更高版本

在 Oracle 19 中，Oracle 引入了执行LISTAGG(DISTINCT <value>)此操作的功能，无需执行子查询来删除任何重复项。

CREATE TABLE temp
(
    date_val    DATE,
    category    VARCHAR (3),
    amount      NUMBER
);

INSERT INTO temp
     VALUES (DATE '2012-01-01', 'ABC', 1000.00);

INSERT INTO temp
     VALUES (DATE '2012-02-01', 'DEF', 500.00);

INSERT INTO temp
     VALUES (DATE '2012-02-01', 'GHI', 800.00);

INSERT INTO temp
     VALUES (DATE '2012-02-10', 'DEF', 700.00);

INSERT INTO temp
     VALUES (DATE '2012-03-01', 'ABC', 1100.00);

column category_list NEW_VALUE categories

SELECT '''' || LISTAGG (DISTINCT category, ''',''') WITHIN GROUP (ORDER BY category) || ''''    AS category_list
  FROM temp;

SELECT *
  FROM (SELECT date_val, amount, category FROM temp) x
       PIVOT (MAX (amount) FOR category IN (&categories)) p;

DROP TABLE temp;

早于 Oracle 19c

CREATE TABLE temp
(
    date_val    DATE,
    category    VARCHAR (3),
    amount      NUMBER
);

INSERT INTO temp
     VALUES (DATE '2012-01-01', 'ABC', 1000.00);

INSERT INTO temp
     VALUES (DATE '2012-02-01', 'DEF', 500.00);

INSERT INTO temp
     VALUES (DATE '2012-02-01', 'GHI', 800.00);

INSERT INTO temp
     VALUES (DATE '2012-02-10', 'DEF', 700.00);

INSERT INTO temp
     VALUES (DATE '2012-03-01', 'ABC', 1100.00);

column category_list NEW_VALUE categories

SELECT '''' || LISTAGG (category, ''',''') WITHIN GROUP (ORDER BY category) || ''''    AS category_list
  FROM (SELECT DISTINCT category
          FROM temp);

SELECT *
  FROM (SELECT date_val, amount, category FROM temp) x
       PIVOT (MAX (amount) FOR category IN (&categories)) p;

DROP TABLE temp;

任一脚本的结果

SQL> @test_tsql_conversion.sql


Table TEMP created.


1 row inserted.


1 row inserted.


1 row inserted.


1 row inserted.


1 row inserted.


       CATEGORY_LIST
____________________
'ABC','DEF','GHI'


old:SELECT *
  FROM (SELECT date_val, amount, category FROM temp) x
       PIVOT (MAX (amount) FOR category IN (&categories)) p
new:SELECT *
  FROM (SELECT date_val, amount, category FROM temp) x
       PIVOT (MAX (amount) FOR category IN ('ABC','DEF','GHI')) p

    DATE_VAL    'ABC'    'DEF'    'GHI'
____________ ________ ________ ________
01-FEB-12                  500      800
10-FEB-12                  700
01-JAN-12        1000
01-MAR-12        1100


Table TEMP dropped.

推荐阅读