SQL Query - CASE WHEN

[MichaelRSnow]

Does anyone know much about writing SQL queries, I have a basic knowledge and trying unsuccessfully to do something

I have created a JOIN to a table of data but i have duplicate ID's with different corresponding values in another column

[TABLE="class: grid, width: 500, align: center"]
[TR]
[TD]ID[/TD]
[TD]System[/TD]
[TD]Channel[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]22222[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[/TABLE]

I want to write a query that searches the ID and returns the channel as a Y or N

,	CASE			WHEN Channel = 1 Then 'Y'
			ELSE 'N'
			END As 'Online'
	,	CASE
			WHEN Channel = 2 Then 'Y'
			ELSE 'N'
			END As 'Direct'

But I'm finding that this not returning any requests on 12345 when on multiple rows applied to multiple channels?

Any ideas would be helpful, can't find anything on Google?

 

[Joe4]

Based on your sample data you posted, what exactly do you want the output to look like?

 

[MichaelRSnow]

Based on your sample data you posted, what exactly do you want the output to look like?

I was hoping the query result to display as follows

[TABLE="class: grid, width: 500, align: center"]
[TR]
[TD]ID[/TD]
[TD]Online[/TD]
[TD]Direct[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]22222[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[/TR]
[/TABLE]

 

[Joe4]

Unfortunately, I no longer have SQL available to me, so I used Access (which is pretty similar - you may need to change one or two things, but the concepts should be the same).
I nested an Aggregate Query, like this:

[COLOR=#ff0000]SELECT x.ID, IIF(x.O>0,"Y","N") as Online, IIF(x.D>0,"Y","N") as Direct
FROM[/COLOR]
[COLOR=#ff0000]([/COLOR][COLOR=#0000ff]SELECT MyTable.ID, Sum(IIf([Channel]=1,1,0)) AS O, Sum(IIf([Channel]=2,1,0)) AS D[/COLOR]
[COLOR=#0000ff]FROM MyTable[/COLOR]
[COLOR=#0000ff]GROUP BY MyTable.ID[/COLOR][COLOR=#ff0000]) as x;[/COLOR]

Note, I do not know what the name of your table is, so I used "MyTable".

 

[MichaelRSnow]

Unfortunately, I no longer have SQL available to me, so I used Access (which is pretty similar - you may need to change one or two things, but the concepts should be the same).
I nested an Aggregate Query, like this:

[COLOR=#ff0000]SELECT x.ID, IIF(x.O>0,"Y","N") as Online, IIF(x.D>0,"Y","N") as Direct
FROM[/COLOR]
[COLOR=#ff0000]([/COLOR][COLOR=#0000ff]SELECT MyTable.ID, Sum(IIf([Channel]=1,1,0)) AS O, Sum(IIf([Channel]=2,1,0)) AS D[/COLOR]
[COLOR=#0000ff]FROM MyTable[/COLOR]
[COLOR=#0000ff]GROUP BY MyTable.ID[/COLOR][COLOR=#ff0000]) as x;[/COLOR]

Note, I do not know what the name of your table is, so I used "MyTable".

Once I get my head round this i'll try to apply those principles, thank you