Kurt Bergman
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:
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
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
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
to...
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.
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.
Well, the number of elements is not dynamic..
Please use table alias names, this increases readability:
This should work:
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;
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);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm on vacation until Friday I'll get back to you as soon as possible
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;
';
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;
';
Hi ConcordCA - It really should be Ste5an getting most or all of the points here. I only pointed out a minor typo.
ASKER
Oops... I meant for it to be Ste5an
Is there a way to change it?
Is there a way to change it?
I don't care that much..
Just use alias names in your queries for better readable code and I'm happy :)
Just use alias names in your queries for better readable code and I'm happy :)
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!
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
It's working great. Thank you again!
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