这个问题在这里已有答案:
在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 answersI 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 scenarioIf 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更多推荐
发布评论