HI, THANK YOU FOR YOU GOOD WEB SITE. IVE LOOKED AT IT, AND WROTE IT AS SHOWN, BUT THIS IS DOESNT WORK (((( , WHAT IS A PROBLEM, AND PLEASE HELP ME TO CHANGE IN PIVOT NULL TO ZERO

SELECT * FROM (SELECT ACCOUNTNUMBER, BRANCHCODE, COMPONENT, BALANCE, GLCODE FROM FCJLIVE.CLTBACCOUNTCOMPBAL_BREAKUP)

PIVOT (SUM(BALANCE) FOR COMPONENT IN ('PRINCIPAL', 'MAININT', 'AQDINT')) PV2 PIVOT (MAX(GLCODE) FOR COMPONENT IN ('PRINCIPAL', 'MAININT')) AS PV2

Hi Paresh, I have the following problem : I have 2 tables, T1 and T2 T1

Question                        QuestionID
How are you 15
What is your name   16
How old are you 17
City                        18
Country                     19

and T2

Response           QuestionID
fine           15
John           16
39         17
New York           18
U.S        19

I want to create an TempTable and alter it dinamically with columns 'Question' and insert data 'Response' like

How are you What is your name	How old are you	City	      Country
fine                        John	                    39	                    New York	      U.S

Can you let me know how can i achieve this? Thanks a lot. Hicham.

The solution provided really isn't appropriate for a pivot. It's more like a column expansion, like this:

SELECT  [p].[seq],
    	CASE WHEN [p].[ItemColour]	= 'Red'				THEN 1 ELSE 0 END as [Red], 
    	CASE WHEN [p].[ItemColour]	= 'Blue'			THEN 1 ELSE 0 END as [Blue], 
    	CASE WHEN [p].[ItemColour]	= 'Green'			THEN 1 ELSE 0 END as [Green], 
    	CASE WHEN [p].[ItemName]	= 'Samsung Moble'	THEN 1 ELSE 0 END as [Samsung Mobile],
    	CASE WHEN [p].[ItemName]	= 'Nokia Moble'		THEN 1 ELSE 0 END as [Nokia Mobile],
    	CASE WHEN [p].[ItemName]	= 'Motorola Mobile'	THEN 1 ELSE 0 END as [Motorola Mobile]
FROM    @Pivot [p]

If you want to pivot on multiple columns on a meaningful way, you have to combine the columns:

SELECT * 
FROM 
    (	SELECT	[p].[ItemCode], 
    			([p].[ItemName]) + '-' + ([p].[ItemColour]) as [desc]
    	FROM	@Pivot [p]
    ) [base]
    PIVOT
    	(	COUNT([ItemCode])
    		FOR [desc] IN 
    			(
    				[Samsung Mobile-Red],	[Samsung Mobile-Green],	[Samsung Mobile-Blue], 
    				[Nokia Mobile-Red],		[Nokia Mobile-Green],	[Nokia Mobile-Blue],
    				[Motorola Mobile-Red],	[Motorola Mobile-Green],	[Motorola Mobile-Blue]
    			)
    	) [pvt]

This results in the more meaningful result:

Samsung Mobile-Red Samsung Mobile-Green Samsung Mobile-Blue Nokia Mobile-Red Nokia Mobile-Green Nokia Mobile-Blue Motorola Mobile-Red Motorola Mobile-Green Motorola Mobile-Blue
------------------ -------------------- ------------------- ---------------- ------------------ ----------------- ------------------- --------------------- --------------------
2                  1                    0                   0                1                  3                 1                   0                     0

Another option is to combine the use of PIVOT for the CUBE operator:

SELECT 
    [ItemName], 
    [All], 
    ISNULL([Red], 0) as [Red], 
    ISNULL([Blue], 0) as [Blue],  
    ISNULL([Green], 0) as [Green]
FROM
    (
    	SELECT		ISNULL([p].[ItemName], 'All')	as [ItemName], 
    				ISNULL([p].[ItemColour], 'All')	as [ItemColor], 
    				COUNT(*)						as [cnt]
    	FROM		@Pivot [p]
    	GROUP BY	[p].[ItemName], [p].[ItemColour]
    	WITH CUBE
    ) [base]
    PIVOT
    (
    	SUM([cnt])
    	FOR [ItemColor] IN ([All], [Red], [Blue], [Green])
    ) pvt

Which returns:

ItemName             All         Red         Blue        Green
-------------------- ----------- ----------- ----------- -----------
All                  8           3           3           2
Motorola Mobile      1           1           0           0
Nokia Mobile         4           0           3           1
Samsung Mobile       3           2           0           1

@hicham4

Given that you will have a large number of respondents and large number of questions, it's probably a better idea to perform the pivot in a reporting tool like SQL Server Reporting Services (using a Tablix), Access (using a CrossTab query) or Excel (using a PivotTable).

nice... pivot using multiple column is new for me. nice concept.