I have this table:
x
SELECT
USER,
DEP
FROM Table_1;
USER | DEP |
---|---|
USER1 | DEP1 |
USER2 | DEP2 |
USER3 | DEP2 |
USER4 | DEP3 |
But i want to transpose it and group user under DEP
.
DEP1 | DEP2 | DEP3 |
---|---|---|
USER1 | USER2 | USER4 |
USER3 | NULL | NULL |
What i have tried following pivot query.
SELECT *
FROM
(
SELECT
USER,
DEP
FROM Table_1
) AS NewTable
PIVOT
(
MAX(USER)
FOR DEP IN ([DEP1],[DEP2],[DEP3])
) AS pvt;
The output is: 00933. 00000 - "SQL command not properly ended"
.
How do i use PIVOT
properly?
Advertisement
Answer
You can give each DEP
type a row number and then pivot:
SELECT dep1,
dep2,
dep3
FROM (
SELECT "USER",
dep,
ROW_NUMBER() OVER ( PARTITION BY dep ORDER BY ROWNUM ) AS rn
FROM table_1
)
PIVOT (
MAX( "USER" )
FOR DEP IN (
'DEP1' AS dep1,
'DEP2' AS dep2,
'DEP3' AS dep3
)
)
ORDER BY rn
Which, for the sample data:
CREATE TABLE table_1 ( "USER", DEP ) AS
SELECT 'USER1', 'DEP1' FROM DUAL UNION ALL
SELECT 'USER2', 'DEP2' FROM DUAL UNION ALL
SELECT 'USER3', 'DEP2' FROM DUAL UNION ALL
SELECT 'USER4', 'DEP3' FROM DUAL;
Outputs:
DEP1 | DEP2 | DEP3 :---- | :---- | :---- USER1 | USER2 | USER4 null | USER3 | null
db<>fiddle here