Filtering output on container codes (e.g. all coded segments produced by speaker 1)

[bozo32]

I did not find an easy way to filter a report on a given code. For example, what did speaker 1 say...which would then spit out all the text segments from speaker one with the codes applied to each contained segment. My SQL is really old, so I worked with GenAI to come up with a script to do it (below). If there is an easier way (perhaps one that spits out the pretty csv already and supports text segments in context) I'd love to hear that I've re-invented the wheel.

-- This script allows you to produce filtered output. This is useful when asking things like 'what does speaker 1 say' or 'what do male respondents say ' when the containing codes can be quite long (speakerID, sex) but the content of interest can be quite short. It dumps all the codes applied into a single column and appends to the bottom a list of all the codes used in the entire output. That list of codes then needs to be turned into column headers and then you can do conditional values in excel using something like this =IF(ISNUMBER(SEARCH("," & E$1 & ",", "," & $D2 & ",")), 1, 0) where E1 has the code of interest and column D has the comma delimited list of codes applied to that segment.

WITH container_codes AS (
-- EDIT THIS LIST ONLY: names of codes you want as CONTAINERS (AND logic)
SELECT 'CONTAINER_CODE_A' AS name -- change 'CONTAINER_CODE_A' to your container code name
UNION ALL
SELECT 'CONTAINER_CODE_B' -- add/remove lines as needed

),

segments AS (
SELECT
ct.ctid,
ct.cid,
ct.fid,
ct.owner,
ct.pos0,
ct.pos1,
ct.seltext
FROM code_text ct
),

segments_with_names AS (
SELECT
s.*,
cn.name AS code_name
FROM segments s
JOIN code_name cn ON cn.cid = s.cid
),

-- all coded segments that have a container code
container_segments AS (
SELECT
swn.*
FROM segments_with_names swn
JOIN container_codes cc
ON swn.code_name = cc.name
),

-- all containment relationships: inner segments inside container segments
contained_raw AS (
SELECT
inner.ctid AS segment_ctid,
inner.fid,
inner.owner,
inner.pos0,
inner.pos1,
inner.seltext AS segment_text,
inner.code_name AS segment_code,
outer.code_name AS container_code
FROM segments_with_names AS inner
JOIN container_segments AS outer
ON inner.fid = outer.fid
AND inner.owner = outer.owner
AND inner.pos0 >= outer.pos0
AND inner.pos1 <= outer.pos1
),

-- keep only segments that are inside ALL container codes (AND)
segments_in_all_containers AS (
SELECT
segment_ctid,
fid,
owner,
pos0,
pos1,
segment_text
FROM contained_raw
GROUP BY segment_ctid, fid, owner, pos0, pos1, segment_text
HAVING COUNT(DISTINCT container_code) = (SELECT COUNT(*) FROM container_codes)
),

-- for those focal segments, attach ALL codes that occur within them
segments_all_codes AS (
SELECT
s.segment_ctid,
s.segment_text,
sc.code_name
FROM segments_in_all_containers s
JOIN segments_with_names sc
ON sc.fid = s.fid
AND sc.owner = s.owner
AND sc.pos0 >= s.pos0
AND sc.pos1 <= s.pos1
)

-- FINAL OUTPUT:
-- (1) one row per segment_ctid, with comma-joined code list
-- (2) one extra row 'ALL_SEGMENTS' listing all codes seen
SELECT
segment_ctid,
segment_text,
GROUP_CONCAT(DISTINCT code_name) AS codes_applied
FROM segments_all_codes
GROUP BY segment_ctid, segment_text

UNION ALL

SELECT
'ALL_SEGMENTS' AS segment_ctid,
'(all segments)' AS segment_text,
GROUP_CONCAT(DISTINCT code_name) AS codes_applied
FROM segments_all_codes

ORDER BY segment_ctid;

[ccbogel]

This is a complex query. I will add it to the default queries.
Please check the default query works as expected.

[bozo32]

I worked on it a bit more...now it outputs an table that can be loaded directly into excel
there is risk that thing won't be aligned right on the output
so I've left it as the code applied in the cells rather than 0/1

WITH
-- 1. List the container codes (AND logic; edit names as needed)
container_codes AS (
-- EDIT THIS: use exact code name(s) from code_name.name
SELECT 'strength' AS name
-- UNION ALL
-- SELECT 'ANOTHER_CONTAINER_CODE'
),

-- 2. Basic segments from code_text
segments AS (
    SELECT
        ct.ctid,
        ct.cid,
        ct.fid,
        ct.owner,
        ct.pos0,
        ct.pos1,
        ct.seltext
    FROM code_text ct
),

-- 3. Attach code names
segments_with_names AS (
    SELECT
        s.*,
        cn.name AS code_name
    FROM segments s
    JOIN code_name cn ON cn.cid = s.cid
),

-- 4. Segments that carry a container code
container_segments AS (
    SELECT
        swn.*
    FROM segments_with_names swn
    JOIN container_codes cc
      ON swn.code_name = cc.name
),

-- 5. All (inner segment, outer container) relationships
contained_raw AS (
    SELECT
        inner.ctid      AS segment_ctid,
        inner.fid,
        inner.owner,
        inner.pos0,
        inner.pos1,
        inner.seltext   AS segment_text,
        inner.code_name AS segment_code,
        outer.code_name AS container_code
    FROM segments_with_names AS inner
    JOIN container_segments AS outer
      ON inner.fid   = outer.fid
     AND inner.owner = outer.owner
     AND inner.pos0 >= outer.pos0
     AND inner.pos1 <= outer.pos1
),

-- 6. Keep only segments that lie inside ALL container codes (AND)
segments_in_all_containers AS (
    SELECT
        segment_ctid,
        fid,
        owner,
        pos0,
        pos1,
        segment_text
    FROM contained_raw
    GROUP BY segment_ctid, fid, owner, pos0, pos1, segment_text
    HAVING COUNT(DISTINCT container_code) = (SELECT COUNT(*) FROM container_codes)
),

-- 7. For those segments, collect ALL codes that occur within them
segments_all_codes AS (
    SELECT
        s.segment_ctid,
        s.segment_text,
        sc.code_name
    FROM segments_in_all_containers s
    JOIN segments_with_names sc
      ON sc.fid   = s.fid
     AND sc.owner = s.owner
     AND sc.pos0 >= s.pos0
     AND sc.pos1 <= s.pos1
)

-- 8. Pivot: one row per segment, one column per code
SELECT
s.segment_ctid,
-- If you want to strip newlines for spreadsheet safety, replace line below with:
-- REPLACE(REPLACE(s.segment_text, x'0A', ' '), x'0D', ' ') AS segment_text,
s.segment_text,

MAX(CASE WHEN a.code_name = 'Pedagogical structure'
         THEN 'Pedagogical structure' ELSE '' END) AS "Pedagogical structure",

MAX(CASE WHEN a.code_name = 'Perspective'
         THEN 'Perspective' ELSE '' END) AS "Perspective",

MAX(CASE WHEN a.code_name = 'change grading'
         THEN 'change grading' ELSE '' END) AS "change grading",

MAX(CASE WHEN a.code_name = 'clarity of expectations'
         THEN 'clarity of expectations' ELSE '' END) AS "clarity of expectations",

MAX(CASE WHEN a.code_name = 'content vs language'
         THEN 'content vs language' ELSE '' END) AS "content vs language",

MAX(CASE WHEN a.code_name = 'critical thinking'
         THEN 'critical thinking' ELSE '' END) AS "critical thinking",

MAX(CASE WHEN a.code_name = 'critique'
         THEN 'critique' ELSE '' END) AS "critique",

MAX(CASE WHEN a.code_name = 'decks'
         THEN 'decks' ELSE '' END) AS "decks",

MAX(CASE WHEN a.code_name = 'differentiate between core/common and context specific examples'
         THEN 'differentiate between core/common and context specific examples'
         ELSE '' END) AS "differentiate between core/common and context specific examples",

MAX(CASE WHEN a.code_name = 'form groups by similarity'
         THEN 'form groups by similarity' ELSE '' END) AS "form groups by similarity",

MAX(CASE WHEN a.code_name = 'fridays more on topic'
         THEN 'fridays more on topic' ELSE '' END) AS "fridays more on topic",

MAX(CASE WHEN a.code_name = 'group'
         THEN 'group' ELSE '' END) AS "group",

MAX(CASE WHEN a.code_name = 'humour'
         THEN 'humour' ELSE '' END) AS "humour",

MAX(CASE WHEN a.code_name = 'instructors'
         THEN 'instructors' ELSE '' END) AS "instructors",

MAX(CASE WHEN a.code_name = 'interesting'
         THEN 'interesting' ELSE '' END) AS "interesting",

MAX(CASE WHEN a.code_name = 'justification'
         THEN 'justification' ELSE '' END) AS "justification",

MAX(CASE WHEN a.code_name = 'learned a lot'
         THEN 'learned a lot' ELSE '' END) AS "learned a lot",

MAX(CASE WHEN a.code_name = 'production values'
         THEN 'production values' ELSE '' END) AS "production values",

MAX(CASE WHEN a.code_name = 'quizzes'
         THEN 'quizzes' ELSE '' END) AS "quizzes",

MAX(CASE WHEN a.code_name = 'relevant to thesis'
         THEN 'relevant to thesis' ELSE '' END) AS "relevant to thesis",

MAX(CASE WHEN a.code_name = 'strength'
         THEN 'strength' ELSE '' END) AS "strength",

MAX(CASE WHEN a.code_name = 'tutorials'
         THEN 'tutorials' ELSE '' END) AS "tutorials",

MAX(CASE WHEN a.code_name = 'use different examples'
         THEN 'use different examples' ELSE '' END) AS "use different examples",

MAX(CASE WHEN a.code_name = 'useful'
         THEN 'useful' ELSE '' END) AS "useful",

MAX(CASE WHEN a.code_name = 'workload'
         THEN 'workload' ELSE '' END) AS "workload"

FROM
segments_in_all_containers s
LEFT JOIN
segments_all_codes a
ON a.segment_ctid = s.segment_ctid
GROUP BY
s.segment_ctid,
s.segment_text
ORDER BY
s.segment_ctid;

[ccbogel]

if you can give me a final, flexible version for others to use, and use " and not ' characters, I can add it to default queries