SHARE
TWEET

Untitled

a guest Mar 16th, 2017 54 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.     SELECT * FROM (
  2.         SELECT ndx.DocumentID
  3.             ,CASE ndx.IndexTypeID
  4.                     WHEN 100001 THEN 'VndNum'
  5.                     WHEN 100002 THEN 'InvNum'
  6.                     WHEN 16 THEN 'Status'
  7.                     WHEN 20 THEN 'DSIJobNum'
  8.                     WHEN 19 THEN 'JobDpt'
  9.                     when 16 then 'PrjMgr'
  10.             END AS FldName
  11.             ,ndx.IndexValue
  12.             ,CASE rte.DocumentID
  13.                 WHEN ndx.DocumentID THEN 1
  14.                 ELSE 0
  15.             END  as InSPCRoute
  16.             ,stat.ID
  17.             ,stat2.ID as IDJobNum
  18.             ,stat3.ID as IDJobDpt
  19.             ,stat4.ID as IDPrjMgr
  20.         FROM [dbo].[DocumentIndex] ndx
  21.         LEFT JOIN ((SELECT [DocumentID]
  22.                 FROM [dbo].[DocumentBinderDocuments]
  23.                 WHERE DocumentBinderID IN
  24.                     (SELECT DocumentBinderID
  25.                         FROM dbo.DocumentRoute
  26.                         WHERE status = 2
  27.                             and DocumentBinderID IS NOT NULL))
  28.                     union
  29.                     (select [DocumentID]
  30.                         from [dbo].[DocumentRoute]
  31.                         where [status] = 2
  32.                             and [DocumentID] is not null)) rte
  33.         ON ndx.DocumentID = rte.DocumentID
  34.         JOIN dbo.DocumentIndex stat
  35.         ON ndx.DocumentID = stat.DocumentID
  36.             and stat.IndexTypeID = 16
  37.         left JOIN dbo.DocumentIndex stat2
  38.         ON ndx.DocumentID = stat2.DocumentID
  39.             and stat2.IndexTypeID = 20
  40.         left JOIN dbo.DocumentIndex stat3
  41.         ON ndx.DocumentID = stat3.DocumentID
  42.             and stat3.IndexTypeID = 19
  43.         left JOIN dbo.DocumentIndex stat4
  44.         ON ndx.DocumentID = stat4.DocumentID
  45.             and stat4.IndexTypeID = 17
  46.         ) AS Source
  47.     PIVOT ( MAX(IndexValue) FOR FldName IN ([VndNum], [InvNum], [Status], [DSIJobNum], [JobDpt], [PrjMgr])) as pvt
  48.     WHERE VndNum IS NOT NULL
  49.         AND Invnum IS NOT NULL
  50.     ORDER BY VndNum, InvNum
RAW Paste Data
Top