Pivots

1

I'm struggling with understanding Pivots. I've read thru what's in Microsoft Docs and a few other sites. I'm pulling in a field from a screen design called 'SCREENSCALE'. Here's an abbreviated view of my query.

,SCREENQUESTIONS.SCREENSCALE
FROM
INQUIRY
INNER JOIN HISParticipant
ON INQUIRY.INQUIRYID = HISParticipant.EntityID
INNER JOIN HISParticipantName
ON HISParticipant.ParticipantID = HISParticipantName.ParticipantID
INNER JOIN INQDocumentATION
ON INQUIRY.INQUIRYID = INQDocumentATION.INQUIRYID
INNER JOIN SCREENDESIGN
ON INQDocumentATION.ScreenDesignID = SCREENDESIGN.SCREENDESIGNID
INNER JOIN SCREENQUESTIONS
ON SCREENDESIGN.SCREENDESIGNID = SCREENQUESTIONS.SCREENDESIGNID
WHERE
INQUIRY.INQDATE >= @INQDATE
AND INQUIRY.INQDATE <= @INQDATE2
AND INQUIRY.Disposition IN (@Disposition)
AND SCREENDESIGN.SCREENDESIGNNAME IN (@SCREENDESIGNNAME)
AND SCREENQUESTIONS.SCREENSCALE IN (@SCREENSCALE)

I'm not getting the results I actually need because I believe I need to convert what I'm getting to Column Headings so that the actual results pull in. I'm trying to pull in data from fields that aren't actual db fields. I really hope I'm explaining this correctly.

SCREENSCALE = current column heading and db field
First Name
Last Name
Due Date

The values that are associated with First Name, Last Name, and Due Date are the actual results I need to get pulled in. I'm guessing that I need to make First Name, Last Name and Due Date the column headings so that the true results are reflected.

Everything I've read includes an aggregate and/or AS function. Is this going to be that complicated (from my perspective)? Again, I hope I explained this correctly.

Thank you.

2

I'm not following.

Could you provide a usable data sample of 2 or 3 rows? [Usable = CREATE TABLE and INSERT statements, not just a "splat" of data on the screen.)

3

I'm guessing that SCREENSCALE is a compound field which you need to split up?
It doesn't sound like a pivot just string manipulation.

If that's the case can you give the structure of SCREENSCALE.

4

I'm trying hard to figure this out and based on additional research, perhaps I need to use the min/max function versus pivot??? Here's the full code.

SELECT
INQUIRY.INQUIRYID
,INQUIRY.INQDATE
,INQUIRY.IncidentDate
,INQUIRY.InquirySource
,INQUIRY.INQTYPE
,INQUIRY.CALLER
,HISParticipantName.LASTNAME
,HISParticipantName.FIRSTNAME
,HISParticipantName.PrimaryYN
,INQUIRY.INQGENERIC4
,INQUIRY.PROVIDERID
,INQUIRY.AGENCY
,INQUIRY.STREET
,INQUIRY.STREET2
,INQUIRY.CITY
,INQUIRY.[STATE]
,INQUIRY.ZIP
,INQUIRY.GENERICTEXT1
,INQUIRY.Notes
,INQUIRY.Jurisdiction
,INQUIRY.STATUS AS [INQUIRY STATUS]
,INQUIRY.Disposition
,SCREENDESIGN.SCREENDESIGNNAME
,INQDocumentATION.DOCDATE
,INQDocumentATION.STATUS AS [INQDocumentATION STATUS]
,SCREENQUESTIONS.SCREENSCALE
FROM
INQUIRY
INNER JOIN HISParticipant
ON INQUIRY.INQUIRYID = HISParticipant.EntityID
INNER JOIN HISParticipantName
ON HISParticipant.ParticipantID = HISParticipantName.ParticipantID
INNER JOIN INQDocumentATION
ON INQUIRY.INQUIRYID = INQDocumentATION.INQUIRYID
INNER JOIN SCREENDESIGN
ON INQDocumentATION.ScreenDesignID = SCREENDESIGN.SCREENDESIGNID
INNER JOIN SCREENQUESTIONS
ON SCREENDESIGN.SCREENDESIGNID = SCREENQUESTIONS.SCREENDESIGNID
WHERE
INQUIRY.INQDATE >= @INQDATE
AND INQUIRY.INQDATE <= @INQDATE2
AND INQUIRY.Disposition IN (@Disposition)
AND SCREENDESIGN.SCREENDESIGNNAME IN (@SCREENDESIGNNAME)
AND SCREENQUESTIONS.SCREENSCALE IN (@SCREENSCALE)
SELECT INQUIRY.INQUIRYID,
MIN(CASE DBCOLUMNNAME WHEN 'Verified Incident Type' THEN DATA END) Verified Incident Type,
FROM SCREENQUESTIONS.SCREENSCALE
GROUP BY INQUIRY.INQUIRYID

Before I start identifying ALL of the new columns I will need, I only selected one of the result values from the 'SCREENSCALE' column to test this out (i.e., Verified Incident Type). Unfortunately, I got a 'failed to preview report...' error message.

"The Value expression for the text box 'INQUIRYID' refers to the field 'INQUIRYID'. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case."

I'll keep researching.