Help with SQL statement to pivot field values into column headers

%3CLINGO-SUB%20id%3D%22lingo-sub-1198571%22%20slang%3D%22en-US%22%3EHelp%20with%20SQL%20statement%20to%20pivot%20field%20values%20into%20column%20headers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1198571%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%20all%2C%20I've%20managed%20to%20filter%20all%20the%20data%20I%20need%20but%20I'm%20struggling%20to%20pivot%20the%20%5BMilestones%5D.%5BName%5D%20column%20into%20headers%20(there%20are%2025%20unique%20values%20in%20this%20field).%E2%80%83%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESyntax%20so%20far%20which%20works%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-02-27%20at%2015.23.24.png%22%20style%3D%22width%3A%20519px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F173898iE702E13238D03817%2Fimage-dimensions%2F519x413%3Fv%3D1.0%22%20width%3D%22519%22%20height%3D%22413%22%20title%3D%22Screenshot%202020-02-27%20at%2015.23.24.png%22%20alt%3D%22Screenshot%202020-02-27%20at%2015.23.24.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20result%20I%20need%20is%20something%20like%20this%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-02-27%20at%2013.45.35.png%22%20style%3D%22width%3A%20531px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F173880iB2996235A9CE22F0%2Fimage-dimensions%2F531x108%3Fv%3D1.0%22%20width%3D%22531%22%20height%3D%22108%22%20title%3D%22Screenshot%202020-02-27%20at%2013.45.35.png%22%20alt%3D%22Screenshot%202020-02-27%20at%2013.45.35.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI've%20tried%20to%20manually%20pivot%20the%20%22Name%22%20field%20using%20case%26nbsp%3B%3C%2FSPAN%3Estatements%2C%20but%20I'm%20not%20trying%20to%20calculate%20the%20values%20so%20I'm%20confused%20about%20the%20need%20to%20use%20aggregate%20functions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20syntax%20alerting%20an%20error%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-02-27%20at%2015.34.44.png%22%20style%3D%22width%3A%20593px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F173901i4CE66D3FF3BB996C%2Fimage-dimensions%2F593x363%3Fv%3D1.0%22%20width%3D%22593%22%20height%3D%22363%22%20title%3D%22Screenshot%202020-02-27%20at%2015.34.44.png%22%20alt%3D%22Screenshot%202020-02-27%20at%2015.34.44.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20%22Milestones%22%20table%20to%20use%20as%20columns%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-02-27%20at%2015.37.33.png%22%20style%3D%22width%3A%20288px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F173903iB785413B9B5F6D4E%2Fimage-dimensions%2F288x177%3Fv%3D1.0%22%20width%3D%22288%22%20height%3D%22177%22%20title%3D%22Screenshot%202020-02-27%20at%2015.37.33.png%22%20alt%3D%22Screenshot%202020-02-27%20at%2015.37.33.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20someone%20please%20take%20a%20look%20and%20correct%20the%20errors%20in%20the%20code%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20would%20appreciate%20any%20assistance%20offered.%20Many%20thanks%20in%20advance%20for%20your%20help!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1198571%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAnalytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EData%20Warehouse%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EReporting%20Services%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1198771%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20SQL%20statement%20to%20pivot%20field%20values%20into%20column%20headers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1198771%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20found%20a%20solution%20for%20part%20of%20the%20problem%20which%20will%20pivot%20the%20Milestones%20into%20column%20headers.%20But%20I'm%20having%20the%20same%20issue%20with%20the%20aggregate%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20display%20the%20DateTime%20instead%20of%20counting%20the%20entries%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-02-27%20at%2017.03.33.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F173918i839960B46BBCB52D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Screenshot%202020-02-27%20at%2017.03.33.png%22%20alt%3D%22Screenshot%202020-02-27%20at%2017.03.33.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

 

Hello all, I've managed to filter all the data I need but I'm struggling to pivot the [Milestones].[Name] column into headers (there are 25 unique values in this field). 

 

Syntax so far which works:

Screenshot 2020-02-27 at 15.23.24.png

 

The result I need is something like this:

Screenshot 2020-02-27 at 13.45.35.png 

 

I've tried to manually pivot the "Name" field using case statements, but I'm not trying to calculate the values so I'm confused about the need to use aggregate functions.

 

This is the syntax alerting an error:

Screenshot 2020-02-27 at 15.34.44.png

 

This is the "Milestones" table to use as columns:

Screenshot 2020-02-27 at 15.37.33.png

 

Could someone please take a look and correct the errors in the code?

 

I would appreciate any assistance offered. Many thanks in advance for your help!

1 Reply

I've found a solution for part of the problem which will pivot the Milestones into column headers. But I'm having the same issue with the aggregate function.

 

How do I display the DateTime instead of counting the entries?

 

Screenshot 2020-02-27 at 17.03.33.png