如何在不应用旋转功能的情况下为不同字段创建列 [英] How to create columns for different fields without applying the pivoting function

问题描述

所以我有一个强硬的心的问题！我有一段时间对此概念有疑问，需要清除...
以下代码显示了使用一种以上语言的学生

So i have a question for the tough hearted! I have had an issue with this concept for awhile and I need it to be cleared... The following code shows students who have more than 1 language

SELECT DISTINCT s.studentnumber as studentnr, 
    p.firstname AS name,
    sl.gradenumber as gradenumber,
    string_agg(DISTINCT l.text, ', ') as languages

FROM student s
    JOIN pupil p ON p.id = s.pupilid    
    JOIN pupillanguage pl on pl.pupilid = p.id
    JOIN language l on l.id = pl.languageid
    JOIN schoollevel sl ON sl.id = p.schoollevelid

GROUP BY s.studentnumber, 
    p.firstname 

结果

现在我的问题很简单，但我不确定答案如何...我想将这些语言分开他们分开字段，因此看起来像这样

Now my question is simple yet I'm not sure how the answer would be... I want to split up those languages and put them in seperate fields so its looks something like this

想要的结果

还有1个主要问题。这使用的是Postgresql 9.3 ，现在不允许分组集或汇总或多维数据集。我尝试了这段代码，但它无法正常工作，我不确定如何处理它，也不确定如何进行操作...

There is also 1 major issue. This is using Postgresql 9.3 which doesnt now allow grouping set or rollup or cube. I tried this code however and its not working correctly, im not sure how to deal with it and im not sure how to go forward about it...

(select distinct l.text 
            from language 
            join pupillanguage pl2 on pl2.languageid = language.id 
            join pupil on p.id = pl2.personid
            limit 1) as language1

这是表格的外观

Language Table 
iD                   PK
shorttext            char varying
text                 char varying

PupilLanguage Table

id                   PK
languageid           FK
personid             FK
displayorder         int

推荐答案

如果列数固定，则可以使用数组：

If you have a fixed number of columns, you can use an array:

select studentnr, name, gradenumber, 
       languages[1] as language_1,
       languages[2] as language_2,
       languages[3] as language_3,
       languages[4] as language_4,
       languages[5] as language_5
FROM (       
  SELECT s.studentnumber as studentnr, 
         p.firstname AS name,
         sl.gradenumber as gradenumber,
         array_agg(DISTINCT l.text) as languages
  FROM student s
      JOIN pupil p ON p.id = s.pupilid    
      JOIN pupillanguage pl on pl.pupilid = p.id
      JOIN language l on l.id = pl.languageid
      JOIN schoollevel sl ON sl.id = p.schoollevelid
  GROUP BY s.studentnumber, p.firstname
) t

请注意，当您使用 group by

这篇关于如何在不应用旋转功能的情况下为不同字段创建列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！