Dynamic Pivot Table woes

Please excuse this newbie's ignorance...
I've looked at several examples and still can't wrap my head around Pivot tables.

I created a view:
SELECT        dbo.tblPMIInspection.PMIInspID, dbo.tblElement.Element, dbo.tblPMIReadings.Reading
FROM            dbo.tblPMIInspection INNER JOIN
                         dbo.tblPMIReadings ON dbo.tblPMIInspection.PMIInspID = dbo.tblPMIReadings.PMIInspID INNER JOIN
                         dbo.tblElement ON dbo.tblPMIReadings.ElementID = dbo.tblElement.ElementID

Open in new window

Element to be the column headings, Reading to be column result, PMIInspID is the Group By column

The column names NEED to be dynamic due to possible increase in number of elements being read etc.

How do I get from
SELECT        PMIInspID, Element, Reading
FROM            vwPMIReadings

Open in new window

Pivot Table Results
Kurt BergmanAsked:
Who is Participating?
SimonConnect With a Mentor Commented:
@Ste5an:  Good thinking :)
one typo in line 1 of  your code:
DECLARE @ColumnList NVARCHAR(MAX) = N''; -- was missing 'n' in the variable name
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Dynamic PivotTables are hard, and if you are a newbie, it's possibly too hard.

Have a read of this article, and once your brain has exploded, if you have any questions, please post them (though I'm taking Christmas off): http://www.experts-exchange.com/Database/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html
Philip's suggested link is a good one, or Google for "TSQL pivot dynamic column names" for other simple examples.
If you can post a sample dataset from your view (with no commercially sensitive data in it) we'll be able to provide a worked example for your data.
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

ste5anSenior DeveloperCommented:
Well, the number of elements is not dynamic..

Please use table alias names, this increases readability:

FROM	dbo.tblPMIInspection I
	INNER JOIN dbo.tblPMIReadings R ON I.PMIInspID = R.PMIInspID 
	INNER JOIN dbo.tblElement E ON R.ElementID = E.ElementID;

Open in new window

This should work:

	FROM	vwPMIReadings
	PIVOT	( SUM(Reading) FOR Element IN ( @ColumnList ) ) P;

SELECT	@ColumnList = @ColumnList + ', ' + Element
FROM	vwPMIReadings
GROUP BY Element;

SET @SqlStatement = REPLACE(@SqlStatement, '@ColumnList', STUFF(@ColumnList, 1, 2, ''));

EXECUTE (@SqlStatement);

Open in new window

Kurt BergmanAuthor Commented:
I'm on vacation until Friday I'll get back to you as soon as possible
Kurt BergmanAuthor Commented:
Thank you very much!

The Elements are dynamic because number and names will change depending on the site where the software is deployed.

There is only one 'Reading' for each Element for a given Inspection (InspID). Thus the need to use MAX (or Min) instead of SUM

SELECT       *
      FROM      vwPMIReadings
      PIVOT      ( max(Reading) FOR Element IN ( @ColumnList ) ) P;
Hi ConcordCA - It really should be Ste5an getting most or all of the points here. I only pointed out a minor typo.
Kurt BergmanAuthor Commented:
Oops... I meant for it to be Ste5an
Is there a way to change it?
ste5anSenior DeveloperCommented:
I don't care that much..

Just use alias names in your queries for better readable code and I'm happy :)
Kurt BergmanAuthor Commented:
This is being displayed in an Access datasheet subform.
I'm looping through the Field names in the view I created to set the Form.Control.ControlSource.  From there I can using the results from the SQL statement you provided to set the Form.Recordset.

It's working great. Thank you again!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.