Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT * FROM (
- SELECT ndx.DocumentID
- ,CASE ndx.IndexTypeID
- WHEN 100001 THEN 'VndNum'
- WHEN 100002 THEN 'InvNum'
- WHEN 16 THEN 'Status'
- WHEN 20 THEN 'DSIJobNum'
- WHEN 19 THEN 'JobDpt'
- when 16 then 'PrjMgr'
- END AS FldName
- ,ndx.IndexValue
- ,CASE rte.DocumentID
- WHEN ndx.DocumentID THEN 1
- ELSE 0
- END as InSPCRoute
- ,stat.ID
- ,stat2.ID as IDJobNum
- ,stat3.ID as IDJobDpt
- ,stat4.ID as IDPrjMgr
- FROM [dbo].[DocumentIndex] ndx
- LEFT JOIN ((SELECT [DocumentID]
- FROM [dbo].[DocumentBinderDocuments]
- WHERE DocumentBinderID IN
- (SELECT DocumentBinderID
- FROM dbo.DocumentRoute
- WHERE status = 2
- and DocumentBinderID IS NOT NULL))
- union
- (select [DocumentID]
- from [dbo].[DocumentRoute]
- where [status] = 2
- and [DocumentID] is not null)) rte
- ON ndx.DocumentID = rte.DocumentID
- JOIN dbo.DocumentIndex stat
- ON ndx.DocumentID = stat.DocumentID
- and stat.IndexTypeID = 16
- left JOIN dbo.DocumentIndex stat2
- ON ndx.DocumentID = stat2.DocumentID
- and stat2.IndexTypeID = 20
- left JOIN dbo.DocumentIndex stat3
- ON ndx.DocumentID = stat3.DocumentID
- and stat3.IndexTypeID = 19
- left JOIN dbo.DocumentIndex stat4
- ON ndx.DocumentID = stat4.DocumentID
- and stat4.IndexTypeID = 17
- ) AS Source
- PIVOT ( MAX(IndexValue) FOR FldName IN ([VndNum], [InvNum], [Status], [DSIJobNum], [JobDpt], [PrjMgr])) as pvt
- WHERE VndNum IS NOT NULL
- AND Invnum IS NOT NULL
- ORDER BY VndNum, InvNum
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement