SQL Transposed Values displayed diagonally

Question

I have table rows that i transposed to columns, but when the values get generated, they are displayed diagonally and i am unsure of why.

Original Table

SELECT * FROM TABLE1;

This is the sql statement i used to transpose the columns: WNS, TNS and NVP

CREATE TABLE FMTABLE AS 
SELECT EXPERIMENT, STAGE, INTSTAGE, SCENARIO,
CASE WHEN TPG LIKE '%ASPN%' THEN WNS END AS ASPN_WNS,
CASE WHEN TPG LIKE '%ASPD%' THEN WNS END AS ASPD_WNS,
CASE WHEN TPG LIKE '%ASDD%' THEN WNS END AS ASDD_WNS,
CASE WHEN TPG LIKE '%SSSS%' THEN WNS END AS SSSS_WNS,
CASE WHEN TPG LIKE '%DDDD%' THEN WNS END AS DDDD_WNS,
CASE WHEN TPG LIKE '%FFFF%' THEN WNS END AS FFFF_WNS,
CASE WHEN TPG LIKE '%GGGG%' THEN WNS END AS GGGG_WNS,
CASE WHEN TPG LIKE '%HHHH%' THEN WNS END AS HHHH_WNS,
CASE WHEN TPG LIKE '%JJJJ%' THEN WNS END AS JJJJ_WNS,
CASE WHEN TPG LIKE '%KKKK%' THEN WNS END AS KKKK_WNS,
CASE WHEN TPG LIKE '%LLLL%' THEN WNS END AS LLLL_WNS,
CASE WHEN TPG LIKE '%EEEE%' THEN WNS END AS EEEE_WNS
FROM TABLE1;

FMTABLE

The columns are correct but the values shows up in a weird way. I am trying to get only 2 rows of data (SCENE01 and SCENE02)

How do i go about transposing the table properly?

Table Generation code

CREATE TABLE TABLE1 (
  EXPERIMENT NVARCHAR(55),
  STAGE NVARCHAR(55),
  INTSTAGE NVARCHAR(55),
  SCENARIO NVARCHAR(55),
  TPG NVARCHAR(55),
  WNS DOUBLE,
  TNS DOUBLE,
  NVP DOUBLE
);
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE01', 'ASPN', 0.5, 0.6, 0.8);
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE01', 'ASPD', 0.5, 0.5, 0.8);
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE01', 'ASDD', 0.5, 0.8, 0.8);
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE01', 'SSSS', 0.5, 0.6, 0.8);
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE01', 'DDDD', 0.5, 0.1, 0.8);
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE01', 'FFFF', 0.5, 0.2, 0.8);
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE01', 'GGGG', 0.5, 0.63, 0.8);
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE01', 'HHHH', '', '', '');
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE01', 'JJJJ', '', '', '');
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE01', 'KKKK', '', '', '');
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE01', 'LLLL', 0.5, 0.8, 0.8);
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE01', 'EEEE', 0.5, 0.98, 0.8);

INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE02', 'ASPN', 0.5, 0.6, 0.8);
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE02', 'ASPD', 0.5, 0.5, 0.8);
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE02', 'ASDD', 0.5, 0.8, 0.8);
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE02', 'SSSS', 0.5, 0.6, 0.8);
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE02', 'DDDD', 0.5, 0.1, 0.8);
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE02', 'FFFF', 0.5, 0.2, 0.8);
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE02', 'GGGG', 0.5, 0.63, 0.8);
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE02', 'HHHH', '', '', '');
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE02', 'JJJJ', '', '', '');
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE02', 'KKKK', '', '', '');
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE02', 'LLLL', 0.5, 0.8, 0.8);
INSERT INTO TABLE1 VALUES ('EXP1', 'STAGE1', 'INTSTAGE1', 'SCENE02', 'EEEE', 0.5, 0.98, 0.8);

Mysql and sqlite are 2 different products. Pls remove the irrelevant tag. — Shadow, Jun 29 '16 at 05:55

score 1 · Accepted Answer · answered Jun 29 '16 at 06:05

If you want to see only 2 rows in the output, then you probably want to aggregate your data by scenario column. Use your conditional statements within an aggregate function. In my example I will use sum(), but feel free to replace it with the one you truly need:

select
    scenario,
    sum(CASE WHEN TPG LIKE '%ASPN%' THEN WNS ELSE 0 END) AS ASPN_WNS
    ...
    from table1
    group by scenario

SQL Transposed Values displayed diagonally

1 Answers1