How to group results in a row?

Question

How to group results in a row?

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

2

I would like to perform a query on the FILE_EXAME table with the following data:

CREATETABLEFICHA_EXAME(FICHAsmallint,EXAMEVARCHAR(15));INSERTINTOFICHA_EXAMEVALUES(1,'Hemograma');INSERTINTOFICHA_EXAMEVALUES(1,'Colesterol');INSERTINTOFICHA_EXAMEVALUES(1,'Ferro');INSERTINTOFICHA_EXAMEVALUES(2,'Colesterol');INSERTINTOFICHA_EXAMEVALUES(3,'Ferro');INSERTINTOFICHA_EXAMEVALUES(3,'Hemograma');INSERTINTOFICHA_EXAMEVALUES(4,'Ferro');

InthequerythatIcreated,Iwouldliketoseewhichexamsarecontainedintheextractedsheetthrougha" Has or does not have ", and do not list the exam in fact, but would like it to appear in only 1 line.

I tried the form below, but for each exam related to the card, it brings me a new line, and I would like it to be displayed dynamically on a single line, regardless of whether there was more than one exam on the same card:

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;

Result :

sql sql-server query

asked by anonymous 10.12.2015 / 14:28

3 answers

Generating valid Php Array for jSON conversion How to ask the reason for an exclusion using confirm and prompt?

score 1 · Answer 1

In this case you will need a Pivot, if you only have these three exams, you can do the following:

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 have to mount the SQL to execute the same thing:

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

score 0 · Answer 2

This happens because its CASE WHEN is executed for each record of the FICHA_EXAME table so it generates different rows because it is comparing different values. One way to get around the problem would be to create a SELECT for each type of exam and make a JOIN. Here 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 by simply adding a WHERE fe.FICHA = ?

score 0 · Answer 3

In this way, you will get the name of the exams dynamically. So you do not have to rewrite the query when you register for 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*/