title: Convert row data to column in SQL Server
body: <p>Today I was asked by my colleague to transform data from a vertical staging table into a horizontal table. I mean transform rows to column. I used PIVOT and resolved it. But got into situation where I am getting trouble to move data if the data field repeats itself.</p>
  
  <p>Here is the test data that I am working on:</p>
  
  <pre><code>CREATE TABLE STAGING 
  (
      ENTITYID INT,
      PROPERTYNAME VARCHAR(25),
      PROPERTYVALUE VARCHAR(25)
  )
  
  INSERT INTO STAGING VALUES (1, 'NAME', 'DONNA')
  INSERT INTO STAGING VALUES (1, 'SPOUSE', 'HENRY')
  INSERT INTO STAGING VALUES (1, 'CHILD', 'JACK')
  INSERT INTO STAGING VALUES (2, 'CHILD', 'KAYALA')
  </code></pre>
  
  <p>I used PIVOT to show row data as columns:</p>
  
  <pre><code>SELECT * FROM 
  (SELECT ENTITYID, PROPERTYNAME, PROPERTYVALUE FROM STAGING) AS T
  PIVOT (MAX(PROPERTYVALUE) FOR PROPERTYNAME IN (NAME, SPOUSE, CHILD)) AS T2
  </code></pre>
  
  <p>The output is:</p>
  
  <pre><code>ENTITYID    NAME    SPOUSE  CHILD
  1           DONNA   HENRY   JACK
  2           NULL    NULL    KAYALA
  </code></pre>
  
  <p>But he wanted the output something like:</p>
  
  <pre><code>ENTITYID    NAME    SPOUSE  CHILD   CHILD
  1           DONNA   HENRY   JACK    KAYALA
  </code></pre>
  
  <p>bottom line is that there can be more than one CHILD attribute coming into the staging table. And we need to consider this and move all the CHILDREN to columns.</p>
  
  <p>Is this possible? </p>