SQLServer - Is it possible to divide the results of the same column?-CodePudding

I have a table containing the input and output parameters of different APIs. Here is the structure of my database :

API : API_ID (PK), API_URI, API_DATE, API_DESCRIPTION

RELATION : API_PAR_ID (PK), APIPAR_API_ID (FK), APIPAR_PAR_ID (FK)

Parametre : PAR_ID (PK), PAR_NOM, PAR_TYPE

The only way to differentiate if a parameter is input or output is the type. If the type is NULL ? It is an output parameter.

So, I would like to know if it is possible in a simple query to make the difference between the two. That is, in my SELECT displayed in one column the name of the input parameters and in another the output parameters, something that could look like this

SELECT
    [PAR_NOM] AS [INPUT]
    , [PAR_NOM] AS [OUPUT]
    , [PAR_type]
FROM Parametre AS P
    INNER JOIN RELATION AS R
        ON P.[PAR_ID] = R.[APIPAR_PAR_ID]
WHERE [APIPAR_PAR_ID] = 10

Or do I have to make two requests? One to get the input parameters, and another one for the output parameters? Knowing that the columns are important because I intend to use them in an API

Sample data :

API : 1, 'API/DOC/V1', '08/02/2022', 'First api'
API : 2, 'API/SERVER/V1', '10/01/2022', 'Api call for the server'
API : 3, 'API/CITY/V2', '15/03/2022', 'Api to get name of cities'

PARAMETRE : 1, CODE_PO, 'string'
PARAMETRE : 2, REF_SO, NULL
PARAMETRE : 3, NB_AR, NULL

RELATION : 1, 1, 1
RELATION : 2, 1, 2
RELATION : 3, 2, 3

Exemple result :

INPUT       OUTPUT      par_type
code_po     NULL        string
NULL        REF_SO      NULL
NULL        NB_AR       NULL

CodePudding user response：

Your question is difficult to understand, but I think you can use a simple case to determine when the parameter is null or not

You can try it out yourself at this Fiddle

select case when p.PAR_TYPE is not null then p.PAR_NOM else null end as INPUT,
       case when p.PAR_TYPE is null then p.PAR_NOM else null end as OUTPUT,
       p.PAR_TYPE
from   Parametre p

CodePudding user response：

In addition to a case, you can use IIF.

SELECT IIF(PAR_type IS NOT NULL, PAR_NOM, NULL) [Input], IIF(PAR_type IS NULL, PAR_NOM, NULL) [Output], PAR_Type FROM Parametre