0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people who like playing board games, designing board games or modifying the rules of existing board games

DECLARE @algorithms TABLE (algorithm NVARCHAR(35) COLLATE SQL_Latin1_General_CP1_CS_AS);
INSERT INTO @algorithms VALUES ('quicksort'), ('mergesort'), ('count'), ('microfocus'), 
('internal-tables'), ('function-module'), ('sap-data-dictionary'), ('sap-selection-screens'), ('webdynpro');
DECLARE @languages TABLE (algorithm NVARCHAR(35) COLLATE SQL_Latin1_General_CP1_CS_AS);
INSERT INTO @languages VALUES ('java'), ('sql'), ('cobol'), ('abap');


WITH tag_names AS (
    SELECT pt.PostId, tag.TagName
    FROM PostTags pt
    JOIN Tags tag ON pt.TagId = tag.Id 
),q AS (
    SELECT question.Id questionId, question.AcceptedAnswerId, a.Algorithm, a.Language
    FROM (
        SELECT t.PostId, t.Algorithm, lang.Language
        FROM (
            SELECT algo.PostId, algo.TagName Algorithm
            FROM tag_names algo 
            WHERE algo.TagName IN (SELECT * FROM @algorithms) 
        ) t
        JOIN (
            SELECT algo.PostId, algo.TagName Language
            FROM tag_names algo
            WHERE algo.TagName IN (SELECT * FROM @languages)
        ) lang ON t.PostId = lang.PostId 
    ) a
    JOIN Posts question ON question.Id = a.PostId
    WHERE AcceptedAnswerId IS NOT NULL
)
SELECT questions.*, answers.Id, answers.Score, answers.Body, STRING_AGG(t.TagName, ',') WITHIN GROUP (ORDER BY t.TagName ASC) Tags_agg
FROM q questions
JOIN Posts answers ON answers.ParentId = questions.questionId 
JOIN tag_names t ON t.PostId = questions.questionId
WHERE answers.Score > 0
GROUP BY questions.questionId, questions.AcceptedAnswerId, questions.Algorithm, questions.Language, answers.Id, answers.Score, answers.Body

fork query

edited jan 30

created jan 5 anonymous