Concatenate values with condition (case)

Asked

Viewed 51 times

0

Good evening! I’m new to the programming.

I have the code below in a CTE that counts the number of subscribers in each area.

In the main consultation I see which area has the largest amount of subscriber.

I would like to return the numerical value as it is already appearing, but concatenate with the name of the area of major interest. But I’m not getting it because one value is integer and the other string. Or at least put the name of the area with more amount in another column. Could you help me? Thank you

WITH CTE AS(

     SELECT TOP 1 INSCRITO.IDPS AS ID,

(SELECT

   COUNT (*) AS A FROM INSCRITO

     WHERE IDAREAINTERESSE = 51 ) AS A,

(SELECT COUNT (*) AS A FROM INSCRITO

    WHERE IDAREAINTERESSE = 52 ) AS B,

(SELECT

   COUNT (*)  FROM INSCRITO

    WHERE IDAREAINTERESSE = 53) AS C

                                          

      FROM INSCRITO) SELECT ID,

                                             (CASE

                                                 WHEN A > B AND A > C THEN  A
                                                 WHEN B > A AND B > C THEN  B
                                                 WHEN C > A AND C > B THEN  C

                                               END) AS MAIOR,

                                                                                                                                                                                                                                       

                                                                                                                                                                                                                       

                                             (CASE

                                              WHEN A < B AND A < C THEN  A
                                              WHEN B < A AND B < C THEN B

                                              WHEN C < A AND C < B THEN  C
                                               END) AS MENOR

                                                                                                                                                                                                                                       


                   FROM CTE
sql string-concatenation case cte

  • "because one value is integer and the other string", then just convert the whole value to string

  • If you are using Postgresql use the function to_char to format its numeric value to string the desired way.

  • What is the database manager?

1 answer

1

Good night,

Here is a suggestion for testing:

WITH 
  CTE_COUNT AS
  (
    SELECT
      IDPS AS ID,
      IDAREAINTERESSE,
      COUNT(*) AS QTDE
    FROM INSCRITO
    WHERE
      IDAREAINTERESSE IN (51, 52, 53)
    GROUP BY
      IDPS,
      IDAREAINTERESSE
  ),

  CTE_RN AS
  (
    SELECT
      *,
      ROW_NUMBER() OVER(PARTITION BY ID ORDER BY QTDE) AS RN_ASC,
      ROW_NUMBER() OVER(PARTITION BY ID ORDER BY QTDE DESC) AS RN_DESC
    FROM CTE_COUNT
  )

SELECT 
  A.ID,
  A.IDAREAINTERESSE AS MENOR_AREA,
  A.QTDE AS MENOR_QTDE,
  D.IDAREAINTERESSE AS MAIOR_AREA,
  D.QTDE AS MAIOR_QTDE
FROM CTE_RN AS A
INNER JOIN CTE_RN AS D
  ON 
    D.ID = A.ID AND
    D.RN_DESC = 1
WHERE
  A.RN_ASC = 1

The first CTE is used to count by ID and area, in the second CTE the Row_number function is used to number the rows in ascending and descending order to identify the lines with the lowest and highest value, and at the end an auto merge is made so that the smaller and larger values are returned in a single row for each ID.

Remarks:

  • avoid if possible consult several times the same table to have a better performance
  • I think the concatenation of values would increase the difficulty to identify the lowest and highest value

I hope it helps

Browser other questions tagged sql string-concatenation case cte

You are not signed in. Login or sign up in order to post.