Using PIVOT in sql
<p>I have following <code>TypeStatusDetails</code> table. This table has different <code>TypeID</code>s. Each type can have max 4 <code>StatusID</code>s (or less i.e. for eg. <code>TypeID</code> 3 has only 3 <code>StatusID</code>s).</p>

<p>RecordCount shows no. of records for specific StatusID.
(eg. 3 Pending Records for Type_1)  </p>

<pre><code>---------------------------------------------------------------
TypeID    Type      StatusID     StatusName    RecordCount
---------------------------------------------------------------
1         Type_1      1          Pending         3
1         Type_1      2         In Process       2
1         Type_1      3         Completed        1
1         Type_1      4         Invalid          1
2         Type_2      1          Pending         4
2         Type_2      2         In Process       5
2         Type_2      3         Completed        6
2         Type_2      4         Invalid          1
3         Type_3      1          Pending         1
3         Type_3      2        In Process        1
3         Type_3      3        Completed         1
</code></pre>

<p>I want result as following table:</p>

<p>Depending on each Type, I want to display RecordCount as per status with StatusID.</p>

<pre><code>--------------------------------------------------------------------------------
Type Type  Status  Pending   Status  InProcess  Status  Completed Status Invalid         ID          ID     Count       ID     Count      ID      Count     ID     Count
            for               for                for               for
          Pending           InProcess          Completed          Invalid
--------------------------------------------------------------------------------
1  Type_1     1       3        2         2         3         1       4       1
2  Type_2     1       4        2         5         3         6       4       1
3  Type_3     1       1        2         1         3         1       4       0
</code></pre>

<p>I have used following query </p>

<pre><code>SELECT *
FROM (
    SELECT 
       TypeID,Type,StatusName,RecordCount
    FROM #TypeStatusDetails
) as s
PIVOT
(
    SUM(RecordCount)
    FOR StatusName IN ([Pending],[In Process],[Completed],[Invalid]) 
)AS pvt
</code></pre>

<p>But I am having trouble to get StatusId column as per each StatusName to combine it in pivot.</p>

<p>Can anyone please help me with this?</p>