Pivot millons of records

Question

I have a table with 4 columns and more than 100 million records. Table Design:

ID char(12) PK 
Type Char(2) PK (Values 1,2,3)
DCID varchar(10) Null
IND Varchar(2) Null (Values Y, N)

This needs to be pivoted like

ID, DCID1, DCID2, DCID3, IND1, IND2, IND3

If Type is having value 1,Then in the Pivoted table the DCID1 should have the value or if Type is 2 DCID2 should have the value and so on. Also correspoding IND also needs to be placed in IND1, IND2, IND3 like that.

How to pivot this?

Answer 1

My suggestion would be to look at using both the UNPIVOT and the PIVOT functions to get the result.

The UNPIVOT will be used to convert the DCI and IND multiple columns into multiple rows in a single column. Once that is done, then you can pivot the data back into columns.

The UNPIVOT code will be similar to this:

select id,
  col +type as new_col,
  value
from
(
  select id,
    type, 
    dcid, 
    cast(ind as varchar(10)) ind
  from yt
) d
unpivot
(
  value
  for col in (DCID, IND)
) unpiv;

See SQL Fiddle with Demo. This gives a result:

|           ID | NEW_COL | VALUE |
----------------------------------
| 1            |  dcid1  |  test |
| 1            |   ind1  |     Y |
| 2            |  dcid2  |   est |
| 2            |   ind2  |     Y |

The new_col contains the DCID and IND names and it has the type value concatenated to the end. This new value will be what you apply the PIVOT to:

select id, DCID1, DCID2, DCID3, IND1, IND2, IND3
from
(
  select id,
    col +type as new_col,
    value
  from
  (
    select id,
      type, 
      dcid, 
      cast(ind as varchar(10)) ind
    from yt
  ) d
  unpivot
  (
    value
    for col in (DCID, IND)
  ) unpiv
) src
pivot
(
  max(value)
  for new_col in (DCID1, DCID2, DCID3, IND1, IND2, IND3)
) piv;

See SQL Fiddle with Demo. The result will be:

|           ID | DCID1 | DCID2 | DCID3 | IND1 | IND2 | IND3 |
-------------------------------------------------------------
| 1            |  test |       |       |    Y |      |      |
| 2            |       |   est |       |      |    Y |      |
| 3            |       |       |  blah |      |      |    Y |
| 4            |   yes |       |       |    N |      |      |
| 5            |       |    hs |       |      |    N |      |
| 6            |       |       |    jr |      |      |    N |