Closed. This question does not meet Stack Overflow guidelines . It is not currently accepting answers.

This question does not appear to be about programming within the scope defined in the help center .

Closed 4 days ago.
Improve this question

Please help me convert Oracle into SQL Server PIVOT.

The table_cases contains these data:

And the expected result is below. Note: I just short-cut the columns "hour-Nth" for "hour-01" to "hour-24" columns.

Oracle:

SELECT 
  "column_user_id", 
  "column_user_name", 
  "column_cases", 
  "column_number_hour", 
  "column_number_index"
FROM 
  table_cases 
  /* Use Pivot to flip the time windows */
  PIVOT (
    listagg(column_cases, ',') within group(
      order by 
        column_number_hour
    ) for column_number_index in (
      0 as "Hour-01", 1 as "Hour-02", 2 as "Hour-03", 
      3 as "Hour-04", 4 as "Hour-05", 5 as "Hour-06", 
      6 as "Hour-07", 7 as "Hour-08", 8 as "Hour-09", 
      9 as "Hour-10", 10 as "Hour-11", 11 as "Hour-12", 
      12 as "Hour-13", 13 as "Hour-14", 14 as "Hour-15", 
      15 as "Hour-16", 16 as "Hour-17", 17 as "Hour-18", 
      18 as "Hour-19", 19 as "Hour-20", 20 as "Hour-21", 
      21 as "Hour-22", 22 as "Hour-23", 23 as "Hour-24"
    )
  )

When I tried to convert it to SQL Server like this. I made a temporary table for this example only if it helps understand my question.

CREATE TABLE #table_cases (
    column_user_id nvarchar(10),
    column_user_name nvarchar(50),
    column_cases int,
    column_number_hour int,
    column_index_hour int
)

INSERT INTO #table_cases (column_user_id, column_user_name, column_cases, column_number_hour, column_index_hour)
VALUES ('AAA111', 'Shelby, Marie', 5, 9, 0); 

INSERT INTO #table_cases (column_user_id, column_user_name, column_cases, column_number_hour, column_index_hour)
VALUES ('BBB222', 'Wayne, Johnny', 15, 10, 1); 

INSERT INTO #table_cases (column_user_id, column_user_name, column_cases, column_number_hour, column_index_hour)
VALUES ('BBB222', 'Wayne, Johnny', 51, 9, 0); 

SELECT * 
FROM 
    (SELECT 
         "column_user_id", 
         "column_user_name", 
         "column_cases", 
         "column_number_hour", 
         "column_number_index"
     FROM 
         #table_cases) AS TABLE_1
  /* Use Pivot to flip the time windows */
 PIVOT (
    STRING_AGG( TABLE_1."column_cases", ';') WITHIN GROUP (ORDER BY TABLE_1."column_number_hour")  
    for TABLE_1.column_number_index in (
      0 as "Hour-01", 1 as "Hour-02", 2 as "Hour-03", 
      3 as "Hour-04", 4 as "Hour-05", 5 as "Hour-06", 
      6 as "Hour-07", 7 as "Hour-08", 8 as "Hour-09", 
      9 as "Hour-10", 10 as "Hour-11", 11 as "Hour-12", 
      12 as "Hour-13", 13 as "Hour-14", 14 as "Hour-15", 
      15 as "Hour-16", 16 as "Hour-17", 17 as "Hour-18", 
      18 as "Hour-19", 19 as "Hour-20", 20 as "Hour-21", 
      21 as "Hour-22", 22 as "Hour-23", 23 as "Hour-24" 
    )
  ) AS PIVOT_TABLE_1

I get the following errors:

Error: Incorrect syntax near ';'

The ORDER BY on line ORDER BY TABLE_1."column_number_hour" also shows an error.

The "0" on the line -> 0 as "Hour-01" also throws an error.

In Oracle the function "listagg" works well in the PIVOT function. But in SQL Server, can we use STRING_AGG inside the PIVOT as aggregate function?

Because it seems to get error. The intention to put alias in the column_number_index "in" also shows error, would it be possible?

SELECT column_user_id, column_user_name, [0] as [Hour-01], [1] as [Hour-02], [2] as [Hour-03], [3] as [Hour-04], [4] as [Hour-05], [5] as [Hour-06], [6] as [Hour-07], [7] as [Hour-08], [8] as [Hour-09], [9] as [Hour-10], [10] as [Hour-11], [11] as [Hour-12], [12] as [Hour-13], [13] as [Hour-14], [14] as [Hour-15], [15] as [Hour-16], [16] as [Hour-17], [17] as [Hour-18], [18] as [Hour-19], [19] as [Hour-20], [20] as [Hour-21], [21] as [Hour-22], [22] as [Hour-23], [23] as [Hour-24] FROM ( SELECT TABLE_1.column_user_id, TABLE_1.column_user_name, TABLE_1.column_number_index, STRING_AGG(TABLE_1.column_cases, ';') WITHIN GROUP (ORDER BY TABLE_1.column_number_hour) AS aggregated_cases FROM ( SELECT column_user_id, column_user_name, column_cases, column_number_hour, column_number_index FROM table_cases ) AS TABLE_1 GROUP BY column_user_id, column_user_name, column_number_index ) G PIVOT ( MAX(G.aggregated_cases) FOR G.column_number_index IN ( [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23] ) ) AS PIVOT_TABLE_1

1条答案

按热度按时间

i2loujxw1#

You are close. The two issues I see are (1) SQL Server PIVOT does not support the STRING_AGG() function and (2) SQL Server does not allow direct aliasing of the values in the FOR clause of the PIVOT .

The first issue can be resolved by performing a GROUP BY and STRING_AGG() separately and then feed that into the PIVOT . The second issue can be resolved by aliasing the PIVOT columns in the final select.

Something like:

Note that the above uses [quoted] style identifiers where needed, as seems to be more customary in SQL server, but "quoted" style identifiers can also be used.

Sample results:
| column_user_id | column_user_name | Hour-01 | Hour-02 | Hour-03 | ... |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| AAA111 | Shelby, Marie | 5 | null | null | ... |
| BBB222 | Wayne, Johnny | 51 | 15 | null | ... |

See this db<>fiddle for a working example.

(Side note: Check your usage of column_number_index vs column_number_hour . The current usage just seemed odd to me.)

赞(0）回复(0）举报 2023-10-15

How to Convert Oracle to MS SQL Server - PIVOT and STRING_AGG [closed]

1条答案

相关问题

热门标签

最新问答