dynamic pivot plsql

Question

Hello I have a very large dataset where I want to dynamicallycreate the columns and place values in the rows.

Below is the dataset

Colorid color
1       Red
1       Blue
1       Yellow
2       Grey
2       Red

This is what I want to return. I think I need a dynamic pivot but not sure about the yes's and no's

Colorid red blue yellow grey 
1       Yes yes  yes
2       Yes no   No     Yes

Thanks

Rarely ever change. So let's say for this purpose no they will not change. FYI in my real program there will be like 100 some colors — , Feb 25 '16 at 02:10
Possible duplicate of [Pivoting rows into columns dynamically in Oracle](http://stackoverflow.com/questions/7730111/pivoting-rows-into-columns-dynamically-in-oracle) — Jon Heller, Feb 25 '16 at 02:55

score 0 · Accepted Answer · answered Feb 25 '16 at 09:40

If you need to use the result in pl sql function or procedure, you can do the following.

DECLARE
 -- Local variables here
 sql_          VARCHAR2(32000);
 l_colCnt      NUMBER;
 l_descTbl     dbms_sql.desc_tab;
 fdbk          NUMBER;
 cur           NUMBER;
 pVarcharValue VARCHAR2(200);
BEGIN
 -- Test statements here
 sql_ := 'select to_char(Colorid) Colorid ';
 FOR c IN (SELECT DISTINCT color FROM COLOR_TABLE ORDER BY color) LOOP
        sql_ := sql_ || ', nvl((select ''yes'' from COLOR_TABLE where Colorid=a.Colorid and color=''' || c.color || '''),''no'') ' || c.color || ' ';
 END LOOP;
 sql_ := sql_ || ' from (select distinct Colorid from COLOR_TABLE) a';

 cur := DBMS_SQL.open_cursor;
 DBMS_SQL.PARSE(cur, sql_, DBMS_SQL.V7);
 dbms_sql.describe_columns(c => cur, col_cnt => l_colCnt, desc_t => l_descTbl);

 FOR i IN 1 .. l_colCnt LOOP
        dbms_output.put(l_descTbl(i).col_name||' '); -- Here name of column
        dbms_sql.define_column(cur, i, pVarcharValue, 200);
 END LOOP;
 dbms_output.put_line(NULL);
 fdbk := DBMS_SQL.execute(cur);
 LOOP
        fdbk := DBMS_SQL.fetch_rows(cur);

        EXIT WHEN fdbk = 0;
        FOR i IN 1 .. l_colCnt LOOP
             dbms_sql.column_value(cur, i, pVarcharValue);
             dbms_output.put(pVarcharValue || ' '); -- Here stored value of current row and column. One cell.
        END LOOP;
        dbms_output.put_line(NULL);
 END LOOP;
END;

If you need sys_refcursor yo can do:

DECLARE
 -- Local variables here
 sql_          VARCHAR2(32000);
 curs          SYS_REFCURSOR;
BEGIN
 -- Test statements here
 sql_ := 'select to_char(Colorid) Colorid ';
 FOR c IN (SELECT DISTINCT color FROM COLOR_TABLE ORDER BY color) LOOP
        sql_ := sql_ || ', nvl((select ''yes'' from COLOR_TABLE where Colorid=a.Colorid and color=''' || c.color || '''),''no'') ' || c.color || ' ';
 END LOOP;
 sql_ := sql_ || ' from (select distinct Colorid from COLOR_TABLE) a';
 OPEN curs FOR sql_; -- here init sys_refcursor
END;

score -1 · Answer 2 · answered Feb 25 '16 at 05:18

Hi we can achieve this using Pivot and IIF condition

Normal Query

DECLARE @Table1 TABLE 
    (Colorid int, color varchar(6))
;

INSERT INTO @Table1
    (Colorid, color)
VALUES
    (1, 'Red'),
    (1, 'Blue'),
    (1, 'Yellow'),
    (2, 'Grey'),
    (2, 'Red')
;

Select Colorid,IIF([Red] IS NOT NULL,'YES','NO')[Red],IIF([Blue] IS NOT NULL,'YES','NO')[Blue],IIF([Yellow] IS NOT NULL,'YES','NO')[Yellow],IIF([Grey] IS NOT NULL,'YES','NO')[Grey] from (
select Colorid, color,ROW_NUMBER()OVER(PARTITION BY color ORDER BY color)RN from @Table1
GROUP BY Colorid, color)T
PIVOT (MAX(RN) FOR color IN ([Blue],[Red],[Yellow],[Grey]))P

Dynamic Query

if object_id('tempdb..#t') is not null
    drop table #t


CREATE  TABLE #t
    (Colorid int, color varchar(6))
;

INSERT INTO #t
    (Colorid, color)
VALUES
    (1, 'Red'),
    (1, 'Blue'),
    (1, 'Yellow'),
    (2, 'Grey'),
    (2, 'Red')
;


DECLARE @statement NVARCHAR(max)
,@columns NVARCHAR(max)

SELECT @columns = ISNULL(@columns + ', ', '') + N'[' + tbl.color + ']'
FROM (
   SELECT DISTINCT color
   FROM #t
   ) AS tbl

SELECT @statement = ' Select Colorid,IIF([Red] IS NOT NULL,''YES'',''NO'')[Red],
IIF([Blue] IS NOT NULL,''YES'',''NO'')[Blue],
IIF([Yellow] IS NOT NULL,''YES'',''NO'')[Yellow],
IIF([Grey] IS NOT NULL,''YES'',''NO'')[Grey] from (
select Colorid, color,ROW_NUMBER()OVER(PARTITION BY color ORDER BY color)RN from #t
GROUP BY Colorid, color)T
PIVOT (MAX(RN) FOR color IN (' + @columns + ')) as pvt'

EXEC sp_executesql @statement = @statement

Question is tagged with `PLSQL` – Pரதீப் Feb 25 '16 at 07:24 — Pரதீப், Feb 25 '16 at 07:24
god this is really getting on me..@MotoGP haven't seen – mohan111 Feb 25 '16 at 09:10 — mohan111, Feb 25 '16 at 09:10

dynamic pivot plsql

2 Answers2