SQL转置值对角显示[重复](SQL Transposed Values displayed diagonally [duplicate])

这个问题在这里已有答案：

在SQLite中回答4个答案

我有表格行，我转换为列，但是当生成值时，它们是对角显示的，我不确定为什么。

原表

SELECT * FROM TABLE1;

这是我用来转置列的sql语句：WNS，TNS和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

列是正确的，但值以奇怪的方式显示。我试图只获得2行数据（SCENE01和SCENE02）

我如何正确地调换桌子？

表生成代码

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);

This question already has an answer here:

Pivot in SQLite 4 answers

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);

最满意答案

如果您只想在输出中看到2行，那么您可能希望按方案列聚合数据。在聚合函数中使用条件语句。在我的例子中，我将使用sum（），但随意用你真正需要的替换它：

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

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

更多推荐