asked on

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

to...

Phillip Burton

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): https://www.experts-exchange.com/Database/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html

Simon

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.

ste5an

Well, the number of elements is not dynamic..

Please use table alias names, this increases readability:

SELECT	I.PMIInspID, 
		E.Element, 
		R.Reading
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:

DECLARE @ColumList NVARCHAR(MAX) = N'';
DECLARE @SqlStatement NVARCHAR(MAX) = N'
	SELECT 	*
	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

ASKER CERTIFIED SOLUTION

Simon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kurt Bergman

ASKER

I'm on vacation until Friday I'll get back to you as soon as possible

Kurt Bergman

ASKER

Simon,
Thank you very much!

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

Also,
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;
      ';

Simon

Hi ConcordCA - It really should be Ste5an getting most or all of the points here. I only pointed out a minor typo.

Kurt Bergman

ASKER

Oops... I meant for it to be Ste5an
Is there a way to change it?

ste5an

I don't care that much..

Just use alias names in your queries for better readable code and I'm happy :)

Kurt Bergman

ASKER

Epilogue:
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!