Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
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?

share|improve this question
    
You might want to look at the following answered question: dba.stackexchange.com/q/875/247 –  Eric Humphrey - lotsahelp Feb 11 '11 at 19:56
    
How are these related? You need a column that relates the information to each other. –  Eric Humphrey - lotsahelp Feb 11 '11 at 21:06
    
@eric, can i pivot on CID? thats what i was trying to do with no luck –  kacalapy Feb 12 '11 at 4:02
1  
You need something common to every record. And you would get back 5 columns with 5 values. –  Eric Humphrey - lotsahelp Feb 12 '11 at 5:09
add comment

1 Answer

up vote 3 down vote accepted

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;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.