How can I pivot this select?

Question

SELECT 
    sc.CID,sc.CodeName as OverviewText,scRAG.CodeName as RAGStatusText 
FROM 
    StatusCode sc
LEFT OUTER JOIN 
    ProjectOverview po 
    ON sc.CID = po.ProjectOverviewCID AND po.ProjectId = 180
LEFT OUTER JOIN 
    StatusCode scRAG 
    ON po.RAGStatusCID = scRAG.CID
WHERE sc.SCID = 18

the above code result sin this:

CID OverviewText    RAGStatusText
153 Cost            Green
154 Requirements    Yellow
155 Schedule        NULL
156 Technical       NULL
157 Testing         NULL

I want it to return one record with 10 fields:

Cost, Green, Requirements, Yellow, Schedule, NULL, Technical, NULL, Testing, NULL

Can i pivot on cid?

Answer 1

I'm not sure if that is exactly what you want. But It gives your expected result. Note you can rename the columns as you please.

SET NOCOUNT ON

declare @t table(
    cid int,
    OverviewText varchar(15),
    RAGStatusText varchar(15)
 )

insert into @t values (153, 'Cost',           'Green');
insert into @t values (154, 'Requirements',   'Yellow');
insert into @t values (155, 'Schedule',       'NULL');
insert into @t values (156, 'Technical',      'NULL');
insert into @t values (157, 'Testing',        'NULL');


Select 
    [153|1] as [xyz],
    [153|2],
    [154|1],
    [154|2],
    [155|1],
    [155|2],
    [156|1],
    [156|2],
    [157|1],
    [157|2]

FROM (
select CAST(cid as varchar) + '|1' type, OverviewText  text from @t
Union
select CAST(cid as varchar) + '|2' type, RAGStatusText text from @t
) as SourceTable
Pivot(
    min(text)
    for type in ([153|1],[153|2], [154|1],[154|2], [155|1],[155|2], [156|1],[156|2], [157|1],[157|2])
) as PivotTable;