How to separate one filed into two fields with the condition 1

[kate8]

Hi All,
I need to select data from a table, process them, then insert into a new table.
When I just select, I used case to process the data, and found out that is not working the way I expected.
I select ID which is the key, the records are group by ID. Each field might have two values, one is ‘Y’, one is ‘N’, when I insert into the new table, records with Y should be inserted into column F1_Yes, records with value ‘N’ should be inserted into column F1_No. it works when I only do one field, when I do the F2, I got double record in each ID. Something must be wrong. I couldn’t figure it out. I have more than 20 fields; separate them into 40 fields in the new table.
Thank you so much for any suggestions and ideas!!!
Here is how I did:

SELECT DISTINCT
ID,
case when F1 = 'Y' then COUNT(F1) else 0 end As F1_Yes,
case when F1 = 'N' then COUNT(F1) else 0 end As F1_No,
case when F2 = 'Y' then COUNT(F2) else 0 end As F2_Yes,
case when F2 = 'N' then COUNT(F2) else 0 end As F2_No

from dbo.Table1

Group by ID,F1,F2

order by ID
If I only do F1, I get
ID F1_yes F1_No
01 11 0
02 44 0
So on, everything is fine. But I do F2, then I get
ID F1_yes F1_No F2_Yes,F2_No
01 11 0 11 0
01 37 0 11 37
02 44 0 0 44
02 70 0 70 0

 

[imex]

Hi,

Try:

SELECT 
    ID,
    COUNT(case when F1 = 'Y' then F1 end) As F1_Yes,
    COUNT(case when F1 = 'N' then F1 end) As F1_No,
    COUNT(case when F2 = 'Y' then F2 end) As F2_Yes, 
    COUNT(case when F2 = 'N' then F2 end) As F2_No
from dbo.Table1
Group by 
    ID

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[SkipVought]

hi,

Group by ID

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}