CODEWITHSUNDEEP
sqlsql-server-2008
Droptopper
Droptopper

Reputation: 11

Need Null Results from 1 table

I have 1 table Test

Cat          Art      Prc    
1           1000      100
2           1000      200
1           1500      150
2           1500      100
1           2000      250
2           2500      250

How do I get a result like this:

Art     Cat(1)    Cat(2)
1000     100       200
1500     150       100
2000     250       NULL  
2500     NULL      250


Select Art, ......
From Test

Upvotes: 0

Views: 42

Answers (2)

Frazz
Frazz

Reputation: 3043

You can use either LEFT JOINs or subSELECTs.

This should solve your needs by using LEFT JOINs

SELECT
  t.Art,
  tc1.Prc AS Cat1,
  tc2.Prc AS Cat2
FROM (SELECT DISTINCT Art FROM Test) t
LEFT JOIN Test tc1 ON tc1.Art=t.Art AND Cat=1
LEFT JOIN Test tc2 ON tc2.Art=t.Art AND Cat=2

An issue that you should be aware of... this query works as expected IF you have a unique or primary key on Test(Art,Cat). You didn't say anything about this in your question, but I assumed it is so.

If you have more than one row per Art - Cat combination, the query will generate strange results. You would have to aggregate Prc in some way, with a sum or an average.

But if the Cat column can contain variable values, then you should look into CROSSTAB / PIVOT queries.

Upvotes: 2

Meysam Tolouee
Meysam Tolouee

Reputation: 579

Declare @Test Table (Cat Int, Art Int , Prc Int)

Insert @Test (Cat, Art, Prc) Values 
(1, 1000, 100),
(2, 1000, 200),
(1, 1500, 150),
(2, 1500, 100),
(1, 2000, 250),
(2, 2500, 250)

;With CTE1 As (
Select Art, Prc FRom @Test Where Cat = 1 ), CTE2 As (
Select Art, Prc FRom @Test Where Cat = 2), CTE3 As (
Select Distinct Art From @Test)
Select CTE3.Art, CTE1.Prc Cat1, CTE2.Prc Cat2
 From CTE3 
Left Join CTE1 On CTE1.Art = CTE3.Art
Left Join CTE2 On CTE2.Art = CTE3.Art

Upvotes: 0

Related Questions