How to group results in a row?

Question

I would like to perform a query About the table FICHA_EXAME with the following data:

CREATE TABLE FICHA_EXAME
(
 FICHA smallint,
 EXAME VARCHAR(15)
);

INSERT INTO FICHA_EXAME VALUES (1,'Hemograma');
INSERT INTO FICHA_EXAME VALUES (1,'Colesterol');
INSERT INTO FICHA_EXAME VALUES (1,'Ferro');
INSERT INTO FICHA_EXAME VALUES (2,'Colesterol');
INSERT INTO FICHA_EXAME VALUES (3,'Ferro');
INSERT INTO FICHA_EXAME VALUES (3,'Hemograma');
INSERT INTO FICHA_EXAME VALUES (4,'Ferro');

In the query I created, I would like to show which exams are contained in the extracted form through a "Has or does not Have", and not list the exam in fact, but would like it to be displayed in only 1 line.

I tried it the way below, but for each exam related to the card, brings me a new line, and I would like it to be displayed dynamically in one line only, regardless if there was more than one exam equal in the same form:

SELECT FICHA, 
      CASE WHEN EXAME = 'Hemograma' THEN 'S' ELSE 'N' END AS 'POSSUI HEMOGRAMA?',
      CASE WHEN EXAME = 'Colesterol' THEN 'S' ELSE 'N' END AS 'POSSUI COLESTEROL?',
      CASE WHEN EXAME = 'Ferro' THEN 'S' ELSE 'N 'END AS 'POSSUI FERRO?'
FROM FICHA_EXAME;

Upshot:

Answer 1

In this case you will need a Pivot, if you only have these three exams, you can do it as follows:

SELECT 
    FICHA, 
    CASE WHEN [Colesterol] IS NULL THEN 'N' ELSE 'S' END AS [POSSUI COLESTEROL?], 
    CASE WHEN [Ferro] IS NULL THEN 'N' ELSE 'S' END AS [POSSUI FERRO?], 
    CASE WHEN [Hemograma] IS NULL THEN 'N' ELSE 'S' END AS [POSSUI HEMOGRAMA?] 
FROM FICHA_EXAME Ficha 
PIVOT (MAX(EXAME) FOR EXAME IN ([Hemograma], [Colesterol], [Ferro])) Colunas

But if you have a dynamic number of exams, you will have to mount SQL to run it:

DECLARE @DinamicSQL AS NVARCHAR(MAX)
WITH CTE_EXAMEs AS (
    SELECT DISTINCT EXAME FROM FICHA_EXAME
), CTE_Indice AS (
    SELECT ROW_NUMBER() OVER (ORDER BY EXAME) as EXAMEID, EXAME FROM CTE_EXAMEs
), CTE_Recur AS (
    SELECT 
        CTE_Indice.EXAMEID, 
        CAST('CASE WHEN [' + CTE_Indice.EXAME + '] IS NULL THEN ''N'' ELSE ''S'' END AS [POSSUI ' + UPPER(CTE_Indice.EXAME) + '?]' AS VARCHAR(MAX)) AS SqlColuna,
        CAST('[' + CTE_Indice.EXAME + ']' AS VARCHAR(MAX)) AS SqlPivot
    FROM CTE_Indice
    WHERE EXAMEID = 1

    UNION ALL

    SELECT 
        CTE_Indice.EXAMEID, 
        CAST(CTE_Recur.SqlColuna + ', CASE WHEN [' + CTE_Indice.EXAME + '] IS NULL THEN ''N'' ELSE ''S'' END AS [POSSUI ' + UPPER(CTE_Indice.EXAME) + '?]' AS VARCHAR(MAX)) AS SqlColuna,
        CAST(CTE_Recur.SqlPivot + ', [' + CTE_Indice.EXAME + ']' AS VARCHAR(MAX)) AS SqlPivot
    FROM CTE_Indice
    JOIN CTE_Recur ON CTE_Indice.EXAMEID = CTE_Recur.EXAMEID + 1
), CTE_SQL AS (
    SELECT TOP 1 * FROM CTE_Recur ORDER BY EXAMEID DESC
)


SELECT @DinamicSQL = 'SELECT FICHA, ' + SqlColuna + ' FROM FICHA_EXAME Ficha PIVOT (MAX(EXAME) FOR EXAME IN (' + SqlPivot + ')) Colunas'  FROM CTE_SQL
EXEC sp_executesql @DinamicSQL

Answer 2

This way, you get the name of the exams dynamically. So you don’t need to rewrite the query when registering a new exam.

DECLARE @ColunasExt AS VARCHAR(MAX); SET @ColunasExt = 'SELECT FICHA, ';
DECLARE @ColunasInt AS VARCHAR(MAX); SET @ColunasInt = 'SELECT FICHA, '

/*Obtém dinamicamente o nome dos exames(colunas colunas sub interna)*/
SELECT @ColunasInt = @ColunasInt + '(CASE WHEN MAX(EXAME) = '''+(EXAME)+ ''' THEN ''S'' ELSE ''N'' END) AS [POSSUI '+EXAME+' ?], '
FROM FICHA_EXAME GROUP BY EXAME
/*Retira a última vírgula*/
SET     @ColunasInt = SUBSTRING(@ColunasInt,1,LEN(@ColunasInt)-1)
SET @ColunasInt = @ColunasInt + ' FROM FICHA_EXAME GROUP BY FICHA,EXAME'

/*Obtém dinamicamente o nome dos exames(colunas colunas sub externa)*/
SELECT @ColunasExt = @ColunasExt + 'MAX([POSSUI '+EXAME+' ?]) AS [POSSUI '+EXAME+' ?], '
FROM FICHA_EXAME GROUP BY EXAME
/*Retira a última vírgula*/
SET     @ColunasExt = SUBSTRING(@ColunasExt,1,LEN(@ColunasExt)-1)
SET @ColunasExt = @ColunasExt + ' FROM ('+ @ColunasInt +')AS SUB GROUP BY SUB.FICHA' 

SELECT( @ColunasExt) /*Mostra o comando que foi montado*/
EXEC( @ColunasExt) /*Executa o comando que foi montado*/

Answer 3

This happens because your CASE WHEN is executed for each table record FICHA_EXAME so it generates different lines because it’s comparing different values. One way around the problem would be by creating a SELECT for each type of exam and doing a JOIN. Below is the script that solves your problem:

SELECT DISTINCT fe.ficha, 
    COALESCE(h."Hemograma", 'N') AS 'POSSUI HEMOGRAMA?',
    COALESCE(c."Colesterol", 'N') AS 'POSSUI COLESTEROL?',
    COALESCE(f."Ferro",'N') AS 'POSSUI FERRO?'
FROM FICHA_EXAME fe
LEFT JOIN (SELECT FICHA, 'S' AS 'Hemograma' FROM FICHA_EXAME WHERE EXAME = 'Hemograma') as h
    on h.FICHA = fe.FICHA
LEFT JOIN (SELECT FICHA, 'S' AS 'Colesterol' FROM FICHA_EXAME WHERE EXAME = 'Colesterol') as c
    ON c.FICHA = fe.FICHA
LEFT JOIN (SELECT FICHA, 'S' AS 'Ferro' FROM FICHA_EXAME WHERE EXAME = 'Ferro') as f
    ON f.FICHA = fe.FICHA
ORDER BY 1;

You can also filter just add one WHERE fe.FICHA = ?