Help with SQL for Pivot Table
Question
Service Studio Version
11.11.0 (Build 42742)

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.  


Solution

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!

Champion

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?  


Hi Ted,

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!

Solution

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!

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.