title: SQL server Pivot on Multiple Columns- Another dimension
body: <p>I am trying to use a pivot on multiple columns. Currently, I am able to achieve this with just one column. My current method returns:</p>
  
  <pre><code>Membership  Item1
  DO1          400
  Neither       21
  Plan A        69
  Plan B        53
  </code></pre>
  
  <p>But here is what I am trying to achieve:</p>
  
  <pre><code>Membership  Item1   Item2   Item3   Item3    Item4    Item5
  DO1          30     300      25        14       10      144
  Neither      35     250      30        30       5       125
  Plan A       50     100      25        25       5       254
  Plan B       100     25      15        25       4       123
  </code></pre>
  
  <p>Here is my code</p>
  
  <pre><code>WITH CTE_SQL
  AS 
  (
      SELECT CaseId
            ,Item1 AS Number1
            ,[Membership] -- ,Item2 AS Number2,Item3 AS Number3,Item4 AS Number4,Item5 AS Number5
            FROM TABLE1
            WHERE [Membership] IS NOT NULL
  )
  SELECT [Membership]
         ,Item1 --,Item2--,Item3 AS 'Item3',Item4 AS 'Item4',Item5 AS 'Item5'
  FROM 
  (
      SELECT CaseId
            ,Number1
            ,[Membership] --,[Item2],[Item3],[Item4],[Item5]
            FROM CTE_SQL
  ) AS p
  PIVOT
  (
      COUNT(CaseId) 
      FOR Number1 IN (Item1)
  ) AS pvt
  ORDER BY [Membership]
  </code></pre>
  
  <p>Any insight?
  Update Here is the table schema</p>
  
  <pre><code>ColumnName  DataType    Allow Nulls
  ..........  ........    ...........
  CaseId    nvarchar(50)      No
  Membership  nvarchar(50)    Yes
  Item1      nvarchar(50)     Yes
  Item2      nvarchar(50)     Yes
  Item3      nvarchar(50)     Yes
  Item4     nvarchar(50)      Yes
  Item5     nvarchar(50)      Yes
  </code></pre>
  
  <hr>
  
  <p>So here is an update to my last. This is my source data:</p>
  
  <pre><code>CREATE TABLE TABLE1
  (CASENumber NVARCHAR(50),Membership NVARCHAR(50),Item1 NVARCHAR(50),Item2 NVARCHAR(50),Item3 NVARCHAR(50),Item4 NVARCHAR(50),Item5 NVARCHAR(50))
  
  INSERT INTO TABLE1
  SELECT 'Case001', 'D01','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case002', 'D01','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case003', 'Neither','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case004', 'Neither','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case005', 'Neither','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case001', 'PlanA','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case002', 'PlanA','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case003', 'PlanB','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case004', 'PlanB','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case005', 'PlanB','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case001', 'D01','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case002', 'D01','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case003', 'Neither','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case004', 'Neither','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case005', 'Neither','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case001', 'PlanA','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case002', 'PlanA','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case003', 'PlanB','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case004', 'PlanB','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case005', 'PlanB','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case003', 'PlanB','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case004', 'PlanB','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case005', 'PlanB','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case005', 'PlanB','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case001', 'D01','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case002', 'D01','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case003', 'Neither','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case004', 'Neither','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case005', 'PlanB','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case005', 'PlanB','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case001', 'PlanB','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case002', 'D01','Gold','Silver','Diamond','Steel','Wood'
  UNION ALL
  SELECT 'Case003', 'Neither','Gold','Silver','Diamond','Steel','Wood'
  </code></pre>
  
  <p>And here I was stuck with my code and the output I have so far:</p>
  
  <pre><code>WITH CTE_SQL
    AS
    (
        SELECT CASENumber,Item1 AS Number1,[Membership]--,Item2 AS Number2,
        Item3 AS  Number3,Item4 Number4,Item5 AS Number5
        FROM  dbo.TABLE1
        WHERE [Membership] IS NOT NULL 
  
    )
    SELECT Membership,Gold as 'Item1' --,Item2--,Item3 AS 'Item3',
    Item4 AS 'Item4',Item5 AS 'Item5' 
    FROM 
    (
        SELECT CaseNumber,Number1,Membership--,[Item2],[Item3],[Item4],[Item5]
        FROM  CTE_SQL 
     ) AS p 
     PIVOT 
     (
        COUNT(CASENumber)
        FOR Number1 in(Gold)  
     ) as pvt
  </code></pre>
  
  <p><strong>Current Output</strong></p>
  
  <pre><code>Membership  Item1
  .........   .....
  D01           7
  Neither       9
  PlanA         4
  PlanB        13
  </code></pre>
  
  <p><strong>Intended Output</strong></p>
  
  <pre><code>Membership  Item1   Item2   Item3   Item4   Item5
  ..........  .....   .....   .....   .....   .....
  D01         7        7        7       7       7
  Neither     9        9       9        9       9
  PlanA       4        4        4       4       4
  PlanB       13      13        13      13      13
  </code></pre>
  
  <p>Please note that all the elements under items1 columns are gold. The same applies to items 2-5 with <code>Silver</code>, <code>Diamond</code>, <code>Steel</code> and <code>Wood</code> respectively.</p>