CODEWITHSUNDEEP
sqlsql-server
Daggle
Daggle

Reputation: 171

SQL How to Bring two results with two differents conditions on two different columns

I Have this SQL Query

SELECT N2 as DOORS, N2 as WINDOWS
   FROM DATABASE.dbo.DISTINTAB
        WHERE numero=45
        AND CODEITEM='D' OR CODEITEM='W'

Right now It brings me 2 columns (DOORS, WINDOWS) with the same results.

What I really want is to bring on the DOORS column only the ITEMS with Code 'D' and on the WINDOWS column only the ITEMS with Code 'W'

How can I achieve this?

Upvotes: 0

Views: 77

Answers (4)

Akina
Akina

Reputation: 42611

In general (when (numero, codeitem) is not defined as unique):

WITH cte AS 
(
SELECT n2, ROW_NUMBER() OVER (PARTITION BY codeitem ORDER BY NULL) rn
FROM DATABASE.dbo.DISTINTAB
WHERE numero=45
  AND codeitem IN ('D', 'W')
)
SELECT t1.n2 as Doors, t2.n2 as Windows
FROM cte t1
FULL OUTER JOIN cte t2 ON t1.rn= t2.rn

If the above fields combination is defined as unique then

SELECT t1.n2 as Doors, t2.n2 as Windows
FROM DATABASE.dbo.DISTINTAB t1
FULL OUTER JOIN DATABASE.dbo.DISTINTAB t2 
    ON t1.numero=45
   AND t2.numero=45
   AND t1.codeitem = 'D'
   AND t2.codeitem = 'W'

Upvotes: 0

Balamurugan Annamalai
Balamurugan Annamalai

Reputation: 36

You can use this query.

Aggregation function used for the result shown in the same row.

SELECT numero, 
       MAX(CASE WHEN CODEITEM = 'D' THEN CODEITEM ELSE '' END ) AS DOORS,
       MAX(CASE WHEN CODEITEM = 'W' THEN CODEITEM ELSE '' END) AS WINDOWS
FROM DISTINTAB
WHERE numero = 45 AND CODEITEM IN ('D', 'W')
GROUP BY numero;

Upvotes: 0

Bhomit Davara
Bhomit Davara

Reputation: 21

You can use the CASE on select

SELECT 
  (CASE WHEN codeitem = 'D' THEN N2 ELSE NULL END) AS DOORS, 
  (CASE WHEN codeitem = 'W' THEN N2 ELSE NULL END) AS WINDOWS
FROM DATABASE.dbo.DISTINTAB
WHERE numero = 45 AND codeitem IN ('D', 'W')

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You seems want aggregation : 

SELECT numero, 
       MAX(CASE WHEN CODEITEM = 'D' THEN N2 END) AS DOORS,
       MAX(CASE WHEN CODEITEM = 'W' THEN N2 END) AS WINDOWS
FROM DATABASE.dbo.DISTINTAB
WHERE numero = 45 AND CODEITEM IN ('D', 'W')
GROUP BY numero;

Upvotes: 1

Related Questions