How pivot row data as columns and fill data dynamically in SQL

Question

I have a table with column names ExDescription,Code,Week00,QtySold. The column Week00 contains values such as Week1,week2,week3,week4 etc ... Week53.

Now, I need to write a query to print the Week00 values as Column names and its values from SUM(QtySold). I tried with something like below, but not getting to the result.

                select * from week_report1            
            pivot (SUM (QtySold) for week_00 in ([Week1],
            [Week2],
            [Week3],
            [Week4],
            [Week5],
            [Week6],
            [Week7],
            [Week8],
            [Week9],
            .........
            [Week52],
            [Week53])) as MaxIn
            where Code in ('99');

I'm attaching excel pivot display of the desired output for better picture of what i want to do.

Are you using MySQL or Oracel (plsql), or something else?

jarlh
– jarlh

2016-06-28 09:35:41 +00:00
Commented Jun 28, 2016 at 9:35 — jarlh
– jarlh, Commented Jun 28, 2016 at 9:35
I'm using Orace (plsql)

Jobin Joseph
– Jobin Joseph

2016-06-28 09:36:28 +00:00
Commented Jun 28, 2016 at 9:36 — Jobin Joseph
– Jobin Joseph, Commented Jun 28, 2016 at 9:36

Blank · Accepted Answer · 2016-06-28 09:43:41Z

1

Not sure if this works for you or not, but try it.(It's for MySQL)

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(IF(week_00 = ''',
      week_00,
      ''', QtySold, 0)) AS `',
      week_00, '`'
    )
  ) INTO @sql
FROM week_report1;
SET @sql = CONCAT('SELECT Code, ', @sql, ' FROM week_report1 GROUP BY Code');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

edited Jun 28, 2016 at 9:43

answered Jun 28, 2016 at 9:35

Blank

12.4k1 gold badge16 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bernd Buffen Over a year ago

good answer - +1 - but you must also escape week_00 with backticks after the AS. when the special chars in like spaces it gives a error

Jobin Joseph Over a year ago

Thanks for the reply, But I'm working with Oracle SQL

MT0 · Accepted Answer · 2016-06-28 10:35:22Z

Oracle Setup:

CREATE TABLE table_name (
  ExDescription VARCHAR2(20),
  Code          INT,
  Week00        VARCHAR2(6),
  QtySold       INT
);

-- Some random data:
INSERT INTO table_name ( ExDescription, Code, Week00, QtySold )
  SELECT DBMS_RANDOM.STRING('X',10),
         99,
         'Week' || LEVEL,
         ROUND( DBMS_RANDOM.VALUE(0,101)-0.5)
  FROM   DUAL
  CONNECT BY LEVEL <= 53;

SELECT * FROM table_name;

EXDESCRIPTION              CODE WEEK00    QTYSOLD
-------------------- ---------- ------ ----------
V01WLMUC50                   99 Week1          74 
MT0HLG35FH                   99 Week2          53 
QSXPKDNDPO                   99 Week3           0 
...

Query:

SELECT *
FROM   ( SELECT Code, Week00, QtySold FROM table_name WHERE code = 99 )
PIVOT ( SUM( QtySold ) FOR Week00 IN (
  'Week1' AS Week1,
  'Week2' AS Week2,
  'Week3' AS Week3
  -- ...
) );

Output

      CODE      WEEK1      WEEK2      WEEK3
---------- ---------- ---------- ----------
        99         74         53          0

Collectives™ on Stack Overflow

How pivot row data as columns and fill data dynamically in SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related