Строки в столбцы, когда данные иногда отсутствуют

Я новичок в синтаксисе Oracle. Итак, я написал в синтаксисе SQL-сервера. Надеюсь, это поможет вам:

CREATE TABLE Project(PRJ_ID int, UDN_ID INT, TXT_VALUE varchar(100));

INSERT INTO Project VALUES (8344,82,'E S'),(8344,69,'A M'),(8364,82,'End'),(8364,59,'Internal');

DECLARE @columns NVARCHAR(MAX), @columns1 NVARCHAR(MAX), @sql NVARCHAR(MAX);

--selecting distinct values and concatenating to get a result like [82],[69],[82]...


SELECT @columns1 = STUFF((
            SELECT DISTINCT ',' + '['+ CAST(UDN_ID AS VARCHAR) + ']' FROM Project
            FOR XML PATH('')
            ), 1, 1, '')
FROM Project;


--using that dynamic column string in the pivot query string


SET @sql = 'SELECT PRJ_ID,' + @columns1 + ' FROM
(
  SELECT * FROM Project
) AS src
PIVOT
(
  MAX(TXT_VALUE) FOR src.UDN_ID IN ('+ @columns1
  + ')
) AS p;';


--executing the pivot query


EXEC sp_executesql @sql;

Это достижимо с помощью PIVOT в оракуле. Пожалуйста, используйте ниже и дайте мне знать в случае каких-либо вопросов.

select * from (
with all_data as(
select '8344' prj_id, 82 id, '40099' txt_val from dual union all
select '8344' prj_id, 64 id, 'E S' txt_val from dual union all
select '8344' prj_id, 178 id, 'End' txt_val from dual union all
select '8364' prj_id, 82 id, '40098' txt_val from dual union all
select '8364' prj_id, 64 id, 'A M' txt_val from dual union all
select '8364' prj_id, 89 id, 'M' txt_val from dual union all
select '8364' prj_id, 178 id, 'Internal' txt_val from dual union all
select '8335' prj_id, 82 id, '40091' txt_val from dual union all
select '8335' prj_id, 64 id, 'E S' txt_val from dual union all
select '8335' prj_id, 89 id, 'N' txt_val from dual union all
select '8335' prj_id, 178 id, 'End' txt_val from dual union all
select '8377' prj_id, 82 id, '40099' txt_val from dual union all
select '8377' prj_id, 64 id, 'Z D' txt_val from dual union all
select '8377' prj_id, 89 id, 'N;M' txt_val from dual union all
select '8377' prj_id, 178 id, 'Internal' txt_val from dual)
select prj_id,id,txt_val from all_data)
pivot
(max(txt_val) 
for id in (82 as "82_val", 64 as "64_val", 89 as "89_val", 178 as "178_val"))
order by 1
;

Способ старомодный (до PIVOT) заключался в агрегировании значения с использованием DECODE (или CASE, для лучшей читабельности). Вот пример (строки 16 и далее - это то, что вы ищете):

SQL> with tbl (prj_id, udn_id, txt_value) as
  2    (select 8344, 82, '13/10/2009' from dual union all
  3     select 8344, 64, 'E S'        from dual union all
  4     select 8344, 178, 'End'       from dual union all
  5     --
  6     select 8364, 82, '12/10/2009' from dual union all
  7     select 8364, 64, 'A M'        from dual union all
  8     select 8364, 89, 'M'          from dual union all
  9     select 8364, 178, 'Internal'  from dual union all
 10     --
 11     select 8335, 82, '05/10/2009' from dual union all
 12     select 8335, 64, 'E S'        from dual union all
 13     select 8335, 89, 'N'          from dual union all
 14     select 8335, 178, 'End'       from dual
 15    )
 16  select prj_id,
 17         max(case when udn_id =  82 then txt_value end) "82",
 18         max(case when udn_id =  64 then txt_Value end) "64",
 19         max(case when udn_id =  89 then txt_value end) "89",
 20         max(case when udn_id = 178 then txt_Value end) "178"
 21  from tbl
 22  group by prj_id;

    PRJ_ID 82         64         89         178
---------- ---------- ---------- ---------- ----------
      8335 05/10/2009 E S        N          End
      8344 13/10/2009 E S                   End
      8364 12/10/2009 A M        M          Internal

SQL>