查询以逗号分隔的值列表 [英] Querying comma-separated lists of values

问题描述

我有一个名为学生"的表，其中包含列名称和技能.Name 是 varchar 类型，Skills 包含 CSV 格式的学生的技能.例如:

I have a table called Students with Columns Name and Skills. Name is varchar type and Skills contains skills of the students in CSV format. Eg:

Name    Skills
ABC     C,Python,JAVA
XYZ     C,Python,C#
UT      C,SQL,JS,CSS

我想显示一个这样的表格

I want to display a a table like this

Name    C     C++     C#      Python      JAVA      SQL        JS        CSS
ABC     1     0       0         1         0        0            0         0
XYZ     1     0       1          1        0        0            0         0

我该怎么做?此外，每个学生都在一所大学学习.我想显示学院名称代替名称列.并且技能下的列需要是每个技能的学生人数的计数.

How do I do this? Also, Each Student studies in a college. I want to display the College name in place of the Name Column. And the Columns under Skills need to be the COUNT of NUMBER of STUDENTS with each SKILL.

推荐答案

我完全同意您真的应该修复您的表格结构的意见.但是，如果您暂时需要一个丑陋的 hack，您可能会像这样:

I fully agree with the comments that you really should fix your table structure. However, if you need an ugly hack for the time being you might get away with something like this:

SELECT
    [Name],
    IIf(Skillz LIKE "*,C,*",1,0) AS C,
    IIf(Skillz LIKE "*,Python,*",1,0) AS Python,
    IIf(Skillz LIKE "*,CSS,*",1,0) AS CSS
FROM
    (
        SELECT 
            [Name],
            "," & Skills & "," AS Skillz
        FROM tblSkills
    )

诀窍是在技能列的开头和结尾处粘贴一个逗号，这样在每种情况下，您只需搜索诸如 "*,C,*" 之类的内容而不是 "C,*" 或 "*,C,*" 或 "*,C".

The trick is to glue a comma onto the beginning and the end of the Skills column so in each case you only search for something like "*,C,*" instead of "C,*" Or "*,C,*" Or "*,C".

此外，请注意有趣的字符"，以防它们混淆 LIKE 运算符或产生麻烦的列名称.

Also, watch out for "funny characters" in case they confuse the LIKE operator or produce troublesome column names.

这篇关于查询以逗号分隔的值列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！