Select same column twice from a table with two different conditions

This query handles the case in which one method (either A or B) is missing for an ID value. It does that by using SELECT DISTINCT and selecting the Hours(A) value and the Hours(B) value for each column for the unique ID. If a value is missing for either A or B, then the result is NULL. If you want something other than NULL to display, use the ISNULL() function and CAST statement. See example of that at the bottom of this solution.

select distinct t1.id as ID,
(Select hours from table_1 As T3 where T3.id=t1.id and T3.method='A') as [Hours(A)],
(Select hours from table_1 As T4 where T4.id=t1.id and T4.method='B') as [Hours(B)]
from Table_1 as T1

Results

Quote:

ID Hours(A) Hours(A)
1 2.50 5.00
2 0.50 1.50
3 3.25 NULL

select distinct t1.id as ID, 
ISNULL(CAST((Select hours from table_1 As T3 where T3.id=t1.id and T3.method='A') as varchar(6)),'N/A') as [Hours(A)],
ISNULL(CAST((Select hours from table_1 As T4 where T4.id=t1.id and T4.method='B') as varchar(6)),'N/A') as [Hours(A)] 
from Table_1 as T1 

Results

Quote:

ID Hours(A) Hours(A)
1 2.50 5.00
2 0.50 1.50
3 3.25 N/A

Tested: SQL Server Express 2012
______________________________________________________________________________________________
SQL Create Table statement

CREATE TABLE [dbo].[Table_1](
	[ID] [int] NULL,
	[Hours] [numeric](8, 2) NULL,
	[Method] [nchar](1) NULL
) ON [PRIMARY]

SQL Insert statements to create test data

insert into table_1 (id,hours,method) values(1,2.5,'A');
insert into table_1 (id,hours,method) values(1,5.0,'B');
insert into table_1 (id,hours,method) values(2,0.5,'A');
insert into table_1 (id,hours,method) values(2,1.5,'B');
insert into table_1 (id,hours,method) values(3,3.25,'A');

Please try this one. Except for "NA" you will get 0.00 there

SELECT ID, SUM(HRS_A) HRS_A, SUM(HRS_B)HRS_B
FROM
(
    SELECT ID,
            CASE WHEN METHOD = 'A' THEN HRS ELSE 0 END HRS_A,
            CASE WHEN METHOD = 'B' THEN HRS ELSE 0 END HRS_B

    FROM TABLE_1

) A
GROUP BY ID

I am not sure if this is exactly what you are looking for, but as far as "Methods" are always "A" and "B" it SHOULD work (don't have the environment to test it right now):

SELECT a.ID, a.HRS, b.HRS 
FROM YOURTABLE a, YOURTABLE b
WHERE a.ID=b.ID AND a.METHOD='A' AND b.METHOD='B'

It basically joins the table with itself and then looks for the "A" and "B" methods.

You can convert various elements in a single column to individual fields by using joins, the fields being related by some common factor that would allow you to correlate them.

For example, in a table where pets are listed by owner

SELECT A.petsName as DOGS, B.petsName as Cats
FROM ANIMALS as A INNER JOIN ANIMALS AS B
ON A.owner = B.owner
WHERE A.petType = 'DOG' and B.petType='cat'

For a time clock, one can have a datetime record for clocking in, another for clocking out, and an employee ID. This method would allow you to create a single record with an intime and outtime field

You'll need to decide what to do for fields that only have a single member of your criteria (a LEFT JOIN and ISNULL() may be an option).

select a.ID as 'HRS(A)',(b.ID) as 'HRS(B)' from HRS a,HRS b where a.ID like '%A%' and b.ID like '%B%'

select
    ID,
    cast( max(hours) as varchar) HrsA,
    case when max(hours)=min(hours) then 'NA' else cast( min(hours) as varchar) HrsB
From tblName
group by ID

Happy coding!
:)