I want to pivot location to make rows that show the pull week and the count of number of packs. Below is the SQL that I am trying to use. The data structure is PullDatesPivotTable.
I am going to post the solution that a co-worked helped me figure out and mark it as the solved solution. Community, please let me know if this is bad form. I am doing in hopes that it may help someone else in the future.
I was trying to refer to the table from my database in my PIVOT statement, but should have been referring to the table I made in the SELECT FROM statement.
I gave that table the alias d and the following PIVOT statement worked:
PIVOT( SUM(NUMPACKS) FOR d.[Label] IN ([40C], [54C], [WAMB], [40CH]) )
Thanks for you help everyone!
Hi Ted,
Btw what error that you encountered? I checked the query looks okay.
In my case, I use outsystems built in aggregate group by and use some looping to populate the local pivot variable.
Hope it helps.
Hey, Ronny,
I am getting the following error when I test the query:
Database returned the following error: Error in advanced query GetPullDates: Incorrect syntax near the keyword 'PIVOT'.
I feel like it is might be in the PIVOT clause?
I think for starters, you need to give your source select an alias
so
..............
group by ..............
) AS SomeAliasName
PIVOT ...........
There might be more going on, I didn't really look at the details of your sql, but start with this.
Dorine
Dorine, you were correct that I needed an alias. But the part that I was really missing was that I needed to use that alias to refer to the table I was pivoting in my PIVOT statement! Thanks for your help!