To make content in a table to appear as heading in result... any idea?

[gorson]

My problem would be something like this. Here, basically I have a table as below:

CustId Question Answer
------------------------
1000 AAAA 1
1000 BBBB 2
1000 CCCC 3
1001 AAAA 2
1001 BBBB 3
1001 CCCC 3

I need to come out with this layout from this table:

CustID AAAA BBBB CCCC
------------------------
1000 1 2 3
1001 2 3 3

Note: The question could be of any content and number of question could be more. How could I do that with Store Proc?

Pls help. Thanks.

[Sandeep M]

There is an option in SQL server 2005
'PIVOT'.

here is the solution

SELECT CustID,  
    [AAA] AS AAA, 
    [BBB] AS BBB, 
    [CCC] AS CCC
    
FROM 
    (SELECT CustID, Answer, Question 
        FROM test10) s 
PIVOT 
( 
    max(answer) 
    FOR Question IN ([AAA],[BBB],[CCC]) 
) p 
ORDER BY [CUSTID]

[gpl]

You need a pivot or crosstab query - depending on your version of SQL, it may be built in, or you will need to search the forum for one.

You could just export your data to Excel and process it there!

[Oralloy]

gorson,

Mr. gpl has the right of it. Sometimes PIVOT TABLE queries are called TRANSFORM queries. There are various idiomatic ways of building them, depending on the SQL that you are using.

I'm assuming SQL Server, so try looking here as a first step.

luck!

[Sandeep M]

Its for variable number of questions. thats
the question field can have
'AAA','BBB','cc c',..... etc (upto the limit of no of columns in a query)

here is the code - tested in -SQL 2005 server

test case :
table test10 (custid int, question varchar(50),ans wer varchar(20))

Enjoy the knowledge freedm !!!!!!!!!!!!

Declare @t VARCHAR(10)
Declare @A VARCHAR(1000)
Declare @B VARCHAR(1000)
-- Fetching All distinct question values in a cursor
DECLARE test1 CURSOR FOR SELECT DISTINCT question FROM test10;
OPEN test1;
--making coulmns for display
FETCH NEXT FROM test1 INTO @t;
set @A='SELECT CustID,'
SET @B='('
WHILE @@FETCH_STATUS = 0
BEGIN
SET @A=@A+ '['+@t+'] as ' + @t +','
SET @B=@B+'['+@t+'],'
FETCH NEXT FROM test1 INTO @t;
END;
-- removing last ',' from both variables
SET @A=SUBSTRING(@A,1,LEN(@A)-1)
SET @B=SUBSTRING(@B,1,LEN(@B)-1)
SET @A=@A+ + ' FROM (SELECT CustID, Answer, Question FROM test10) s  PIVOT (max(answer) FOR Question IN ' +@B+')) p ORDER BY [CUSTID]; '
exec(@A);
CLOSE test1;
DEALLOCATE test1;