September 12, 2013 at 3:12 am
Can you please explain how PIVOT is used in this query? i.e. logic behind the working of PIVOT in the query.
SELECT *
FROM (
SELECT IsNull(St.alternateStudyCode, St.studyCode) AS StudyCode,
St.clientStudyReference as SponsorStudyCode,
Cl.clientName AS Sponsor,
SAFV.txtValue,
SAF.extractName
FROM Activities AS Ac
INNER JOIN Studies AS St ON (St.studyIncId = Ac.studyIncId AND St.studySqlId = Ac.studySqlId)
AND St.isDeleted = 0x0
AND St.studyStartDate < DateAdd(Day,1,@ToDate) -- study initiation date <= the end date
AND (St.studyEndDate is null or St.studyEndDate >= @FromDate) -- study completion date >= the start date
LEFT JOIN StudiesClients as StCl ON StCl.StudySqlId = St.studySqlId AND StCl.studyIncId = St.StudyIncId AND StCl.isDeleted = 0x0
AND StCl.sponsorRanking = 1
LEFT JOIN Clients AS Cl ON Cl.clientSqlId = StCl.clientSqlId AND Cl.clientIncId = StCl.clientIncId AND StCl.isDeleted = 0x0
LEFT JOIN StudiesCategories as StCa ON StCa.studyCategorySqlId = St.studyCategorySqlId AND StCa.studyCategoryIncId = St.studyCategoryIncId AND StCa.isDeleted = 0x0
LEFT JOIN StudiesStatuses AS StSt ON StSt.studyStatusSqlId = St.studyStatusSqlId AND StSt.studyStatusIncId = St.studyStatusIncId AND StSt.isDeleted = 0x0
LEFT JOIN StudiesAdditionalFieldsValues As SAFV
LEFT JOIN StudiesAdditionalFields AS SAF ON SAF.studyAdditionalFieldSqlId = SAFV.studyAdditionalFieldSqlId AND SAF.studyAdditionalFieldIncId = SAFV.studyAdditionalFieldIncId AND SAF.isDeleted = 0x0
--Study link to Site
LEFT JOIN CboValues AS StCbo ON SAFV.cboRecordSqlId = StCbo.cboValueSqlId AND SAFV.cboRecordIncId = StCbo.cboValueIncId AND StCbo.isDeleted = 0x0
ON SAFV.studySqlId = St.studySqlId AND SAFV.studyIncId = St.studyIncId AND SAFV.isDeleted = 0x0
AND SAF.extractName = 'GLPSite'
LEFT JOIN Operators AS Op ON Op.operatorSqlId = Ac.todoBySqlId AND Op.operatorIncId = Ac.todoByIncId AND Op.isDeleted = 0x0
INNER JOIN TypesOfActivities AS TypOA WITH(NOLOCK)
ON TypOA.typeOfActivitySqlId=Ac.typeOfActivitySqlId AND TypOA.typeOfActivityIncId=Ac.typeOfActivityIncId AND TypOA.isDeleted=0x0
AND TypOA.typeOfActivityCode = 'EAS-1'
WHERE Ac.isDeleted = 0x0AND (
(@siteSqlId = StCbo.cboValueSqlId AND @siteIncId = StCbo.cboValueIncId)
OR
(@siteSqlId = AcCbo.cboValueSqlId AND @siteIncId = AcCbo.cboValueIncId))
) As SourceTable
PIVOT (
Min(TxtValue)
FOR extractName In (
[TestItem],
[TestSystem],
[Crop],
[Organism],
[QASystem]
)
) As PivotTable
September 16, 2013 at 8:23 am
Can anybody suggest a alternate query without using PIVOT in the existing query? Urgent help is appreciated.
September 16, 2013 at 8:41 am
The query seems complicated but the pivot is really simple. Personally, I prefer CROSS TABS. You can read more about them on this article http://www.sqlservercentral.com/articles/T-SQL/63681/
and here's an example:
SELECT IsNull(St.alternateStudyCode, St.studyCode),
SponsorStudyCode,
Sponsor,
MIN( CASE WHEN extractName = 'TestItem' THEN TxtValue) AS TestItem,
MIN( CASE WHEN extractName = 'TestSystem' THEN TxtValue) AS TestSystem,
MIN( CASE WHEN extractName = 'Crop' THEN TxtValue) AS Crop,
MIN( CASE WHEN extractName = 'Organism' THEN TxtValue) AS Organism,
MIN( CASE WHEN extractName = 'QASystem' THEN TxtValue) AS QASystem
FROM TableAndLotsOfJoins
GROUP BY IsNull(St.alternateStudyCode, St.studyCode),
SponsorStudyCode,
Sponsor
September 17, 2013 at 3:39 am
Thanks Luis Cazares, for your prompt reply. That helped me lot.
September 17, 2013 at 9:27 am
You're welcome. I'm glad that I could help.
September 20, 2013 at 3:12 am
Hi Luis,
I have a doubt.
How to post the SQL part and the result set as in a SSMS environment, as you have posted in your post? Please help me as it can be handy in future.
September 20, 2013 at 8:35 am
You need to use the IFCode tags that you can find to the left of the post editor.
For SQL Code, you need [ code="sql"][/code] (without the space between the bracket and code).
That will respect multiple spaces and tabs and will highlight most reserved words.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply