CODEWITHSUNDEEP
sqlsql-servert-sql
PJD
PJD

Reputation: 775

Grouping 2 lines in to 1

This may just be a Friday thing but I cant work out how to display these records as one line, my data looks like this

PTaskID     Part Requisition    Service Requisition
394512            Yes                   No
394512            No                   Yes

What I want is 1 row with the PTaskID but with the following, if there are more rows as in the example and one of them has a Yes in the Part Requisition then the overall row value for Part Requisition is Yes and the same logic should apply to Service Requisition.

Any help would be great PJD

CREATE TABLE [dbo].[Load](
[PTaskID] [int] NOT NULL,
[Part Requisition] [varchar](3) NOT NULL,
[Service Requisition] [varchar](3) NULL
) ON [PRIMARY]


INSERT INTO Load (PTaskID, [Part Requisition], [Service Requisition])
VALUES
(394512,    'Yes',  'No'),
(394512,    'No',   'Yes')

Upvotes: 1

Views: 55

Answers (3)

Azar
Azar

Reputation: 1867

This works better with any collation and also perfect

Select Ptaskid, 
        convert(varchar,max(CONVERT(varbinary, [Part Requisition]))),
        convert(varchar,max(CONVERT(varbinary, [Service Requisition])))

from [Load]
group by ptaskid

Upvotes: 0

Azar
Azar

Reputation: 1867

Try this

 Select Ptaskid, 
        case when sum(case when [Part Requisition] = 'Yes' then 1 else 0 end)> 0 then  'Yes' else 'No' end 'Part Requisition',
        case when sum(case when [Part Requisition] = 'Yes' then 1 else 0 end)> 0 then 'Yes' else 'No' end 'Service Requisition'
from [Load] 
group by ptaskid

Upvotes: 1

StuartLC
StuartLC

Reputation: 107267

Given that 'Yes' comes after 'No' (*in most collations), you could GROUP and then do a Max, viz

SELECT PTaskID, MAX([Part Requisition]) as [MaxPartRequisition], 
                MAX([Service Requisition]) as  [MaxServiceRequisition]
FROM [Load]
GROUP BY PTaskID;

SqlFiddle here

Upvotes: 0

Related Questions