0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Tridion developers and administrators

SELECT TOP 5
			QId, 
			QCreationDate,
			QOwnerUserId,
			QAnswerCount,
			QViewCount,
			QCommentCount,
			QFavoriteCount,
			QTags,
			QScore,
			QTitle,
			QBody,
			Type1 AS QInitialTitle,
			Type2 AS QInitialBody,
			Type3 AS QInitialTags
		FROM (
		  SELECT
			q.Id AS QId, 
			q.CreationDate AS QCreationDate,
			q.OwnerUserId AS QOwnerUserId,
			q.AnswerCount AS QAnswerCount,
			q.ViewCount AS QViewCount,
			q.CommentCount AS QCommentCount,
			q.FavoriteCount AS QFavoriteCount,
			q.Tags AS QTags,
			q.Score AS QScore,
			q.Title AS QTitle,
			q.Body AS QBody,
			CONCAT('Type', qh.PostHistoryTypeId) AS QPostHistoryTypeId,
			qh.Text AS QText
		  FROM
			Posts q
			JOIN  
			PostHistory qh
			ON (q.Id = qh.PostId)
		  WHERE
			q.PostTypeId = 1 AND -- Return questions
			q.ClosedDate IS NULL AND -- that hasn't been closed
			qh.PostHistoryTypeId in (1, 2, 3) -- and retrieving initial title, body and tags
		) t
		PIVOT (MIN(QText) FOR QPostHistoryTypeId IN (Type1, Type2,Type3)) AS pivotField

fork query

edited oct 18 17

created oct 18 17 anonymous