Hello, If anyone is familiar with the SQL pivot command with dynamic columns, you’ll know that you need to first create a query of your columns in an XML Path format, then later use that result in the second query where you pivot your row data into its respective columns. I have tried implementing this in the report designer and have had no luck. I have tried using nested queries where the pivot command references the result of the parent query as a parameter. This almost works but the XML that results does not embed the column names into the data. Here is the snippit of code and XML. (Note: the images show hard-coded columns in the PIVOT command for testing. Using the parent query {Products} as a parameter returns an incorrect syntax by the designer possibly because the designer adds ‘@P2’ in quotes to reference the third parameter and PIVOT command needs the columns to be specified without quotes)
<?xml version="1.0" ?>
<sample-data>
<!--This is the raw data used to create the report preview.-->
<EndDate>2017-05-30 09:44:29</EndDate>
<Report>
<Gateway>Ignition-SERVER</Gateway>
<Name>Test</Name>
<Path>Test</Path>
<Timestamp>2017-05-30 09:44:29</Timestamp>
</Report>
<StartDate>2017-05-25 09:44:29</StartDate>
<query>
<row-0>
<Products>[03081],[03086],[22605],[23016],[32400],[81010]</Products>
<sub_query>
<row-0>
<DATE>2017-05-25</DATE>
<__>0</__>
<__>0</__>
<__>0</__>
<__>292</__>
<__>27</__>
<__>243</__>
</row-0>
</sub_query>
</row-0>
</query>
</sample-data>
Note that while columns with data are returned, they do not contain the names of the columns provided. This makes stacked bar chart not display anything.
For reference, this is what SQL Mgmt. Studio returns:
Any help is appreciated…even if this involves scripting.
Thanks,
David