PIVOT Example

drop table #productqual
SELECT pp.pid,pname,pdescription,pprice [Price],pminprice [MinimumPrice], p_shipable [Shippable],p_renewable [Renewable],
q_header + '-' + q_name as q_name,pl.q_id
into #productqual FROM products pp
inner join ProductQualification pq on pq.pid=pp.pid inner join av_qualif pl on
pq.q_id=pl.q_id
--where pp.pid=643


declare @BMCodeList as varchar(8000)
declare @QryPivot1 as varchar(8000)
SET @BMCodeList=''
SELECT  @BMCodeList = @BMCodeList + '[' + bmcode + '],'
FROM (SELECT distinct q_name bmcode FROM #productqual)a ORDER BY BMCode
IF LEN(@BMCodeList)>0
BEGIN
SET  @BMCodeList = LEFT(@BMCodeList,len(@BMCodeList)-1)
print @BMCodeList

Set @QryPivot1='SELECT PID, pname,pdescription,Price,minimumprice,shippable,renewable,' + @BMCodeList + '
FROM #productqual
PIVOT
(
min(q_id)
FOR q_name IN ('+@BMCodeList+')
) AS PivotedTable;'
exec(@QryPivot1)
END

If want to show 0 or 1 in pivot out columns then--

drop table #productqual
SELECT pp.pid,pname,pdescription,pprice [Price],pminprice [MinimumPrice], p_shipable [Shippable],p_renewable [Renewable],
q_header + '-' + q_name as q_name,pl.q_id
into #productqual FROM products pp
inner join ProductQualification pq on pq.pid=pp.pid inner join av_qualif pl on
pq.q_id=pl.q_id
--where pp.pid=643


declare @BMCodeList as varchar(8000)
declare @BMCodeList1 as varchar(8000)
declare @QryPivot1 as varchar(8000)
SET @BMCodeList=''
SET @BMCodeList1=''

SELECT  @BMCodeList = @BMCodeList + '[' + bmcode + '],'
FROM (SELECT distinct q_name bmcode FROM #productqual)a ORDER BY BMCode

SELECT  @BMCodeList1 = @BMCodeList1 + '[' + bmcode + ']= case when  [' + bmcode + '] is null then ''0'' else ''1'' end' + ','
FROM (SELECT distinct q_name bmcode FROM #productqual)a ORDER BY BMCode


IF LEN(@BMCodeList)>0
BEGIN
SET  @BMCodeList = LEFT(@BMCodeList,len(@BMCodeList)-1)
SET  @BMCodeList1 = LEFT(@BMCodeList1,len(@BMCodeList1)-1)


Set @QryPivot1='SELECT PID, pname,pdescription,Price,minimumprice,shippable,renewable,' + @BMCodeList1 + '
FROM #productqual
PIVOT
(
min(q_id)
FOR q_name IN ('+@BMCodeList+')
) AS PivotedTable;'
exec(@QryPivot1)
END

Another Example-

SELECT @DealerCount =Count(*) from #tempCM

SET @BMCodeList=''
SELECT  @BMCodeList = @BMCodeList + '[' + bmcode + '],'
FROM (SELECT DISTINCT BMCode from  #tempCM)a ORDER BY BMCode

IF LEN(@BMCodeList)>0
BEGIN
SET  @BMCodeList = LEFT(@BMCodeList,len(@BMCodeList)-1)

Set @QryPivot1='SELECT Department, [Description],InputBoxNumber,upperlimit,lowerlimit,
InputReference, linkable, [Month],[Year], OrderReference ,' + @BMCodeList + '
INTO #tempPivotA FROM #tempCM
PIVOT
(
min(curvalue)
FOR BMCode IN ('+@BMCodeList+')
) AS PivotedTable;'