How do I pivot this dataset into columns for each question

sorry if this has already been posted but I've been through umpteen posts on pivoting the past day and still havn't been able to get the result i want.

Background:

In short, I am developing a set of tables that will store a questionnaire dynamically. I wont go into detail of it probably isnt relative.

I basically want to query the table that stores the user input for a set question. These questions branch off each other allowing me to show columns and rows per question etc.

Anyway this query:

SELECT qr.*, Question   
    FROM QuestionRecord qr
    INNER JOIN 
    QuestionRecord P
    ON P.ID = qr.ParentQuestionRecordId
    JOIN Questions q ON q.ID = qr.QuestionID

Produces this result set :

ID FormRecordId QuestionId ParentQuestionRecordId   Value            Question    
---------------------------------------------------------------------------------------
2     1           31            1                Consultancy      Eligible project costs
3     1           32            2                NULL             Date
4     1           33            2                25000            Cash Costs £
5     1           34            2                NULL             In Kind Costs £
6     1           35            2                25000            Total Costs
7     1           31            1                Orchard day x2   Eligible project costs
8     1           32            7                NULL             Date
9     1           33            7                15000            Cash Costs £
10   1           34            7                NULL             In Kind Costs £
11   1           35            7                15000            Total Costs

I basically want to Pivot(I think) these rows to look like so:

Eligible project costs    Date     Cash Costs £     In Kind Costs   Total Costs
--------------------------------------------------------------------------------
Consultancy               NULL        25000            NULL           25000
Orchard day x2            NULL        15000            NULL           15000

I have tried:

SELECT [Eligible project costs],[Date],[Cash Costs £],[In Kind Costs £],[Total Costs]
FROM
(
    SELECT  qr.*, Question
    FROM QuestionRecord qr
    INNER JOIN 
    QuestionRecord P
    ON P.ID = qr.ParentQuestionRecordId
    JOIN Questions q ON q.ID = qr.QuestionID    
)pvt

PIVOT
(
    MIN(Value)
    FOR Question IN
    ([Eligible project costs],[Date],[Cash Costs £],[In Kind Costs £],[Total Costs])
)pivotTable

but this returns each column on a seperate row:

Eligible project costs    Date     Cash Costs £     In Kind Costs   Total Costs
--------------------------------------------------------------------------------
Consultancy               NULL        NULL              NULL             NULL        
NULL                      NULL        NULL              NULL             NULL 
NULL                      NULL       25000              NULL             NULL
NULL                      NULL        NULL              NULL             NULL
NULL                      NULL        NULL              NULL            25000

So that's as close as i have managed to 开发者_开发问答get with it, i was wondering if you guys/girls could help me out :)

Thanks!

Try the following changes to your script (strikethrough = deleted, bold = added):

SELECT [Eligible project costs],[Date],[Cash Costs £],[In Kind Costs £],[Total Costs]
FROM
(
    SELECT  qr.*,
      grp = ROW_NUMBER() OVER (PARTITION BY qr.QuestionId ORDER BY qr.ID),
      Value,
      Question
    FROM QuestionRecord qr
    INNER JOIN 
    QuestionRecord P
    ON P.ID = qr.ParentQuestionRecordId
    JOIN Questions q ON q.ID = qr.QuestionID    
)pvt

PIVOT
(
    MIN(Value)
    FOR Question IN
    ([Eligible project costs],[Date],[Cash Costs £],[In Kind Costs £],[Total Costs])
)pivotTable

I think it must give your the result you are after.

Change SELECT qr.*, Question to SELECT Value, Question. PIVOT groups by the remaining columns.

what you need, like andriy kinda pointed out, is something to make each record unique depending on how you want them grouped. now, if this is a survey system i'm going to guess that you've got some sort of id to identify who the record belongs to. the reason why it's returning on seperate rows is that you have unique records for each row based on those ids, what you need is to add the respondent id to your derived table and get rid of your other id's.

see my example:

declare @table table (ID int identity(1,1), QuestionID int, value varchar(50), Respondent int)
declare @questions table (QID int, name varchar(50))


insert into @questions values (31,'Eligible project costs')
insert into @questions values (32,'Date')
insert into @questions values (33,'Cash Costs')
insert into @questions values (34,'In Kind Costs')
insert into @questions values (35,'Total Costs')

insert into @table values (31,'Consultancy',1)
insert into @table values (32,null,1)
insert into @table values (33,25000,1)
insert into @table values (34,null,1)
insert into @table values (35,25000,1)
insert into @table values (31,'Orchard day x2',2)
insert into @table values (32,null,2)
insert into @table values (33,15000,2)
insert into @table values (34,null,2)
insert into @table values (35,15000,2)



select 
[Eligible project costs],[Date],[Cash Costs],[In Kind Costs],[Total Costs]

from
(
select
    Respondent,
    q.name,
    t.Value
from @table t
    inner join @questions q
        on t.QuestionID=QID
) a
pivot
(
    min(Value)
    for name in ([Eligible project costs],[Date],[Cash Costs],[In Kind Costs],[Total Costs])
) p