For a SQL data-CodePudding

Table structure
N_ID V_KHMC V_SJR V_KWID N_FS
1 a unit threes a publication 1
A unit of zhang SAN b journal 2
3 a unit a journal 3
4 b unit li si a publication 1
5 c b unit, dick, and harry publication 2
Unit 6 b b journal 1
...

Want to draw TAB
V_KHMC V_SJR journals b journal publication c
A unit of zhang SAN 1 2
A unit of 3
Li si 1 b unit 2
Unit 1 b

Pray god give a SQL

CodePudding user response:

A publication b journal publication c

The three are fixed? Or is there more?

CodePudding user response:

Line and column, this post old many, look for previous post, I think I have come back several

CodePudding user response:

reference 1st floor wmxcn2000 response:

a publication b journal publication c

The three are fixed? Or is there more?

Multiple, is another table maintenance data

CodePudding user response:

 select V_KHMC,V_SJR, 
Max (case when V_KWID='a publication then N_FS end) of a publication, 
Max (case when V_KWID='b journals' then N_FS end) b journal, 
Max (case when V_KWID='c journals' then N_FS end) c journal 
The from the TAB 
Group by V_KHMC V_SJR;

CodePudding user response:

 select t.v _khmc, t.v _sjr, 
(sum (case t.v _kwid when 'a publication then t.n _fs else null end)) a publication, 
(sum (case t.v _kwid when 'b journals' then t.n _fs else null end)) b journal, 
(sum (case t.v _kwid when 'c journals' then t.n _fs else null end)) b journal, 
The from table1 t 
Group by t.v _khmc, t.v _sjr 
The order by t.v _khmc

Give it a try

CodePudding user response:

reference 4 floor js14982 response:

 select V_KHMC, 
V_SJR, 
Max (case when V_KWID='a publication then N_FS end) of a publication, 
Max (case when V_KWID='b journals' then N_FS end) b journal, 
Max (case when V_KWID='c journals' then N_FS end) c journal 
The from the TAB 
Group by V_KHMC V_SJR;

A great god, and if V_KWID from another table maintenance data, dynamic V_KWID how to write

CodePudding user response:

If the value is not too much, more intuitive way is
Put all the value CASE WHEN it again

CodePudding user response:

Select * from t_test pivot (sum (n_fs) sumsal/*, avg (salary) avgsal */for (V_kwid) in (' a journal, journal 'b', 'c journals'));