Andy K Andy K - 4 months ago 15
SQL Question

sql - Data with more than one record

I have the following temporary table

enter image description here

Aim is to flag the data with more than one records and put

More than one records


In my example below, if
Siren
appears more than once, I would have

Siren ETS_RS Voie Ville nom_etp
348177155 POITOU-CHARENTES ENGRAIS P.C.E. (SNC) BOULEVARD WLADIMIR MORCH 17000 LA ROCHELLE More than one records


For records that are appearing once, I will have the single name of the company (here
nom_etp
)

Siren ETS_RS Voie Ville nom_etp
344843347 PRESTIGE AUTO ROCHELAIS (SAS) 4 RUE JEAN DEMEOCQ 17000 LA ROCHELLE NIGER


I tried a few things based on the idea that if I can have a count of more than one, I could flag them easily and use them with a
CASE
:

First: I tried to do a count

WITH cte_ssrep_moraux AS (...)
SELECT SIREN,ETS_RS,Voie,Ville
,Denomination AS nom_etp,COUNT(SIREN)
FROM cte_ssrep_moraux
GROUP BY ETS_RS,Voie,Ville,Denomination,SIREN


It hits a snitch as all counts were equal to one and I have the same dataset as in the picture...

Second:

WITH cte_ssrep_moraux AS (...)
SELECT ETS_RS,Voie,Ville
,Denomination AS nom_etp,SIREN,
RANK() OVER (PARTITION BY ETS_RS ORDER BY ETS_RS ASC) AS xx
FROM cte_ssrep_moraux
GROUP BY ETS_RS,Voie,Ville,Denomination,SIREN


It hits a snitch as all counts were equal to one and I have the same dataset as in the picture...

I'm bit confused on what I should do next. I have the feeling will be an easy one and I'll face palmed myself.

Many thanks for reading my question

Answer

Use CTE for this purpose

;WITH CTE AS(
SELECT ETS_RS,Voie,Ville,Denomination AS nom_etp,SIREN,
    ROW_NUMBER() OVER (PARTITION BY ETS_RS ORDER BY ETS_RS ASC) AS  RN
FROM cte_ssrep_moraux
--GROUP BY ETS_RS,Voie,Ville,Denomination,SIREN
)
SELECT ETS_RS,
   Voie,Ville,
   CASE WHEN RN > 1 THEN 'More than one records'
        ELSE nom_etp
        END AS 'nom_etp',
        SIREN
FROM CTE