Pivot in multiple inner joins

Question

Pivot in multiple inner joins

aznabeel

SSC Veteran

Points: 203
More actions
January 19, 2020 at 12:03 pm

#3716589
this is my query, which gives me the complains details
SELECT *
FROM CI_TD_ENTRY TE
INNER JOIN CI_TD_DRLKEY CTD
ON(TE.TD_ENTRY_ID = CTD.TD_ENTRY_ID)
INNER JOIN CI_CASE CC
ON (CTD.KEY_VALUE = CC.CASE_ID);
Now i want to know,
- Number of Complaints - complaints type wise in 2017 , 2018,2019
- Avg closing time of complaints - complaints type wise in 2017 , 2018,2019
I believe it can be done through pivot but i do not know how to do it.
Need help??

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

I personally don't advocate the use of the PIVOT operator because it's not friendly and it's frequently almost twice as slow as the old "Black Arts" method of doing a CROSSTAB.

Here's an article on both methods.

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%e2%80%93-converting-rows-to-columns-1

Here's a follow-up article on how to do it dynamically.

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

--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)

as_1234 SSCrazy Points: 2859 More actions · Answer 2

If further assistance would be useful then can you provide some sample data? I'm not sure of the data that's kept in each table.

This reply was modified 4 years, 10 months ago by as_1234. Reason: Rephrased reply

drew.allen SSC Guru Points: 76987 More actions · Answer 3

In addition, PIVOT can only do one aggregate, but you need two different aggregates in this example:

Number of complaints
Average closing time

Crosstabs are more flexible in addition to what Jeff already said about them being easier and faster.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA