title: How can I do SQL PIVOT formatting the result based on the condition of a field?
body: <p>I have the following data in a query that needs further work</p>
  
  <pre><code>Id          User                    Application           CompanyId         ApplicationId Restricted
  ----------- ----------------------- --------------------- ----------------- ------------- -----------
  1           yes@way.com             members               4027              1             0           
  2           dg@spw.com              members               1104              1             0
  3           dg@spw.com              reports               1104              2             0 
  4           dg@spw.com              identity              1104              3             0
  5           dg@spw.com              landing page          1104              4             0
  6           dg@spw.com              authorization         1104              7             0
  7           jtize@sssssss.com       members               1104              1             0
  8           jtize@sssssss.com       reports               1104              2             0
  9           jtize@sssssss.com       identity              1104              3             0
  10          jtize@sssssss.com       landing page          1104              4             1
  </code></pre>
  
  <p>I was able to get the following using SQL PIVOT but it doesn't quite fulfill the needs of the application.</p>
  
  <p>PIVOT Query (Note the results have been truncated to simplify)</p>
  
  <pre><code>WITH UserData AS
  (
   SELECT ISNULL(CAST((ROW_NUMBER() OVER (order by u.Id, a.Id)) as int), 0) 
          as Id, u.UserName AS [User], 
          a.Description AS Application, ca.CompanyId, u.Id AS UserId, a.Id AS ApplicationId
      FROM  dbo.Application AS a LEFT OUTER JOIN
          dbo.CompanyApplication AS ca ON ca.ApplicationId = a.Id RIGHT OUTER JOIN
          dbo.AspNetUsers AS u ON u.CompanyId = ca.CompanyId
  )
  SELECT *
  
    FROM (
    SELECT [User], [CompanyId], [ApplicationId], [Application]
    FROM  (SElect * from UserData where id &lt; 13) x
  ) as s
  PIVOT (
    MIN([ApplicationId])
    FOR [Application] in (
      [members], [identity], [admin], [contractor qualification], [audits], 
           [landing page], [sitetracker], [reports], [authorization]
    )
  ) 
  as pvt
  </code></pre>
  
  <p>Results:</p>
  
  <pre><code>User                members  identity  landing page  reports     authorization
  ------------------- -------- --------- ------------  ----------- -------------
  dg@spw.com          1        3         4             2           7
  jtize@sssssss.com   1        3         4             2           0
  yes@way.com         1        NULL      NULL          NULL        NULL
  </code></pre>
  
  <p>The real output needs to be one of three  values(applicationid, 0, and -1). 
  The needed result is as follows</p>
  
  <pre><code>User                members  identity  landing page  reports     authorization
  ------------------- -------- --------- ------------  ----------- -------------
  dg@spw.com          1        3         4             2           7
  jtize@sssssss.com   1        3        -1             2           0
  yes@way.com         1        0         0             0           0
  </code></pre>
  
  <p>Note the -1 is for the restricted column </p>