--Function Script 
Create Function Dbo.GetAccessoryType(@AccessoryTypeId Int)
Returns Varchar(10)
As
Begin 
	Declare @AccessoryType Varchar(10)
	Select @AccessoryType = AccessoryType From AccessoryTypes Where AccessoryTypeId = @AccessoryTypeId
	return @AccessoryType
End 
--Function Script End Here

--Pivot Script
	/* Intial Creating Schema  */
	Create Table Cars
	(
		CarId Int,
		CarMake Varchar(20), 
		CarModel Varchar(20)
	)
	Go
	Create Table AccessoryTypes
	(
		AccessoryTypeId Int, 
		AccessoryType Varchar(20)
	)
	Go 
	Create Table Accessories 
	(
		AccId Int , 
		CarId Int , 
		AccessoryTypeId Int , 
		AccessoryName Varchar(20)
	) 

	/* Inserting the Records in the Table for Testing */

	Insert Cars
	Select 10, 'Honda', 'Accord' Union All 
	Select 11, 'Toyta', 'Corolla'

	Insert AccessoryTypes
	Select 2, 'Engine' Union All
	Select 3, 'Exterior' Union All
	Select 4, 'Interior'

	Insert Accessories
	Select 13, 10, 2, 'SomeValve' Union All
	Select 15, 10, 3, 'CoolPaint' Union All
	Select 17, 10, 4, 'SomeGadget' Union All
	Select 18, 11, 2, 'SomeGadget'


	-- Processing of the Query 
	Declare @sQry Varchar(8000),
			@AccessoryTypeId Int , 
			@Counter Int 

	Select @sQry = 'Select C.CarMake,C.CarModel, ', @AccessoryTypeId =0 , @Counter=0

	While @AccessoryTypeId < (Select Distinct Max(AccessoryTypeId) From Accessories )
		Begin 
			Select @AccessoryTypeId = Min(AccessoryTypeId) From Accessories Where AccessoryTypeId > @AccessoryTypeId
			Select @Counter = @Counter+1
			Select @sQry = @sQry + 'Max(Case When AccessoryTypeId = ' + Cast(@AccessoryTypeId As Varchar(10)) +
								   ' Then AccessoryTypeId  End ) As AccessoryTypeId' + Cast (@Counter As Varchar(10)) + 
								  ', Max(Case When AccessoryTypeId = ' + Cast(@AccessoryTypeId As Varchar(10)) + 
									' Then Dbo.GetAccessoryType(AccessoryTypeId) End ) As AccessoryTypes' + Cast (@Counter As Varchar(10)) + ', '
		End 

	Select @sQry = left(@sQry,len(@sQry)-1)
	Select @sQry = @sQry + ' From Accessories A Inner Join Cars C On A.CarId = C.CarId  Group by CarMake, CarModel'
	-- Output...
	Exec(@sQry)
	print @sQry

	---Droping the Table
	--Drop Table Cars		
	--Drop Table AccessoryTypes
	--Drop Table Accessories 

--Pivot Scripts End here..