Help with Pivoting Table

Question

Post reply

Help with Pivoting Table

carlton 84646

Ten Centuries

Points: 1026
More actions
July 3, 2022 at 7:55 am

#4058084
Hi all,
- This topic was modified 1 year ago by carlton 84646. Reason: sensitive data

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 1

Hello Community,

Sorry for supplying so much data in the sample table - I have noticed it has slowed down the page when I scroll.

Anway, I was wondering if I could get some help with this question please.

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 2

I recommend starting here: https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-converting-rows-to-columns-1

Then follow up with the second article here: https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

Instead of trying to use the PIVOT operator you can use a simple GROUP BY with conditional aggregates to achieve the desired results.

 --==== Sample Only
 Select MontaguOwner
      , [Action - 1. Analysing] = max(Case When CurrentOpportunityStatus = 'Action - 1. Analysing' Then LastDateStatusChanged End)
   From temptable3
  Group By
        MontaguOwner

You can add the additional conditions as needed.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 3

Hi Jeff,

I think I'll be able to build on what you've provided for the other conditions.

Thanks

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

First, really nice job on providing "Readily Consumable Data" AND it actually works without error! 😀

With respect to the output you posted, are you looking to pivot ONLY those rows where the CurrentOpportunityStatus column starts with the word "Action"?

Also, do you need for other columns to be included? For example, should the OpportunityName name be included in the output?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 5

Hi Jeff,

Using the same test data I provided I wonder if you could help me build on your code to achieve an additional outcome.

This reply was modified 1 year ago by carlton 84646. Reason: update

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 6

First, really nice job on providing "Readily Consumable Data" AND it actually works without error!

My pleasure

If I'm asking the community for help, I think it's only fair to make it as easy for you guys/girls to provide the help I need.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

carlton 84646 wrote:

Hi Jeff,
Using the same test data I provided I wonder if you could help me build on your code to achieve an additional outcome.
So, on the test data, I simply added the following code to filter out a field to help someone (if not you) help me:
SELECT
*
FROM dbo.opportunitiespresentation
WHERE opportunitiespresentation.MontaguOwner = 'Christian Wolf'
The provided the following table:
I would like to build on your code to achieve the following:
If you could just kick me off, I'm sure I'll be able to take it from there.
Thanks

Seems like we should get the first issue solved before doing anything else. In order for me to answer that, I need you to answer my previous questions. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 8

Jeff Moden wrote:

First, really nice job on providing "Readily Consumable Data" AND it actually works without error! 😀

Thanks

This reply was modified 1 year ago by carlton 84646. Reason: update

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Here's the query to cover your first request.

 SELECT  MontaguOwner                 = IIF(MontaguOwner>'',MontaguOwner,'*** None Given ***')
        ,OpportunityName
        ,[Action - 1. Analysing]      = MAX(IIF(CurrentOpportunityStatus LIKE 'Action - 1.%',CONVERT(CHAR(10),LastDateStatusChanged,101),''))
        ,[Action - 2. Trying to meet] = MAX(IIF(CurrentOpportunityStatus LIKE 'Action - 2.%',CONVERT(CHAR(10),LastDateStatusChanged,101),''))
        ,[Action - 3. Date agreed]    = MAX(IIF(CurrentOpportunityStatus LIKE 'Action - 3.%',CONVERT(CHAR(10),LastDateStatusChanged,101),''))
        ,[Action - 4. Post meeting]   = MAX(IIF(CurrentOpportunityStatus LIKE 'Action - 4.%',CONVERT(CHAR(10),LastDateStatusChanged,101),''))
        ,[Action - 5. Chopped]        = MAX(IIF(CurrentOpportunityStatus LIKE 'Action - 5.%',CONVERT(CHAR(10),LastDateStatusChanged,101),''))
   FROM dbo.temptable3
  GROUP BY MontaguOwner,OpportunityName
  ORDER BY MontaguOwner,OpportunityName
;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 10

Jeff Moden wrote:

First, really nice job on providing "Readily Consumable Data" AND it actually works without error! 😀

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 11

Jeff Moden wrote:

carlton 84646 wrote:

Hi Jeff,

Using the same test data I provided I wonder if you could help me build on your code to achieve an additional outcome.

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 12

Jeff Moden wrote:

Here's the query to cover your first request.

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 13

Jeff Moden wrote:

Here's the query to cover your first request.

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 14

Jeff Moden wrote:

Here's the query to cover your first request.