How to run dynamic pivot table without using exec(sql statment)

Question

Post reply

How to run dynamic pivot table without using exec(sql statment)

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

February 4, 2020 at 3:40 am

#3722813

I working on sql server 2012

I need to run query below without using exec(sql statment)

I try that but i face problem on single quotes on this line '+@column+'

my statment i need to run as below :

DECLARE @Columns as VARCHAR(MAX)
SELECT @Columns =
COALESCE(@Columns + ', ','') + QUOTENAME(FeatureName)
FROM
--distinct FT.FeatureName 
(select  distinct FT.FeatureName  from #InputData Feat inner join #ItemFeatures ItemF
on ItemF.CustomerId=Feat.CustomerId INNER join #FeatureType FT on ItemF.FeatureId=FT.FeatureId

   ) AS B
   ORDER BY B.FeatureName

SELECT ItemCode, IPN,PartnerName,CustomerName, ' + @Columns + '
FROM
(
  select F.ItemId,F.ItemId as ItemCode,I.IPN,I.PartnerName,I.PartnerPart,c.CustomerName,t.FeatureName  from #InputData Itm 
 inner join #ItemFeatures F on F.CustomerId=Itm.CustomerId  
 inner join #Items I on I.ItemID=F.ItemId
 inner join #FeatureType T on T.FeatureId=F.FeatureId 
 inner join #customers c on c.CustomerID=F.CustomerID 
) as PivotData
PIVOT
(
   COUNT(ItemId)
   FOR FeatureName IN (' + @Columns + ')
) AS PivotResult
ORDER BY CustomerName

DECLARE @Columns as VARCHAR(MAX)
SELECT @Columns =
COALESCE(@Columns + ', ','') + QUOTENAME(FeatureName)
FROM
--distinct FT.FeatureName 
(select  distinct FT.FeatureName  from #InputData Feat inner join #ItemFeatures ItemF
on ItemF.CustomerId=Feat.CustomerId INNER join #FeatureType FT on ItemF.FeatureId=FT.FeatureId

   ) AS B
   ORDER BY B.FeatureName

DECLARE @SQLs as VARCHAR(MAX)

SET @SQLs = 'SELECT ItemCode, IPN,PartnerName,CustomerName, ' + @Columns + '
FROM
(
  select F.ItemId,F.ItemId as ItemCode,I.IPN,I.PartnerName,I.PartnerPart,c.CustomerName,t.FeatureName  from #InputData Itm 
 inner join #ItemFeatures F on F.CustomerId=Itm.CustomerId  
 inner join #Items I on I.ItemID=F.ItemId
 inner join #FeatureType T on T.FeatureId=F.FeatureId 
 inner join #customers c on c.CustomerID=F.CustomerID 
) as PivotData
PIVOT
(
   COUNT(ItemId)
   FOR FeatureName IN (' + @Columns + ')
) AS PivotResult
ORDER BY CustomerName'

EXEC(@SQLs)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63509 More actions · Answer 1

Just wondering, but why are you doing a PIVOT in T-SQL at all? Why not do it in the presentation layer (Excel, SSRS etc)?

if you absolutely have to do it in T-SQL, maybe read Jeff Moden's articles on Crosstabs?

If you really want help, how about the source table you're trying to write the dynamic SQL against?

FFS, you post like you just got here.

This reply was modified 4 years, 10 months ago by pietlinden.

hardikssit1 Say Hey Kid Points: 669 More actions · Answer 2

What is the exact error you are getting ?

I have answered your question in other post. I think it contains the same issue.

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 3

Simply stated, I know of no way to create a dynamic pivot/crosstab without dynamic SQL and I know of no way to execute dynamic SQL without the use of EXEC.

I understand the needs for the dynamic columns but pivots and crosstabs do notsupport such a thing without dynamic SQL, period.

With that, I'll ask why can't you use EXEC/EXECUTE?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)