CODEWITHSUNDEEP
sqlsql-serversql-server-2008
jenswirf
jenswirf

Reputation: 7317

How to make a long table wide without aggreagation?

I have this table of financial transactions..

    PersonID  |  SeqId   | FundId | PortfolioDbu |    Date
    ----------------------------------------------------------
      456     |    1     |   B    |     0.1      |  2012-04-03
      456     |    1     |   F    |     0.5      |  2012-04-03
      456     |    1     |   H    |     0.3      |  2012-04-03
      456     |    1     |   Z    |     0.1      |  2012-04-03
       8      |    1     |   B    |     0.5      |  2012-03-23
       8      |    1     |   A    |     0.5      |  2012-03-23
       8      |    2     |   C    |     0.3      |  2011-03-24
       8      |    2     |   X    |     0.3      |  2011-03-24
       8      |    2     |   F    |     0.4      |  2011-03-24
      6001    |    1     |   J    |     0.5      |  2008-01-01
      6001    |    1     |   R    |     0.5      |  2008-01-01
       76     |    1     |   A    |     0.25     |  2010-09-26
       76     |    1     |   B    |     0.25     |  2010-09-26
       76     |    1     |   C    |     0.25     |  2010-09-26
       76     |    1     |   D    |     0.25     |  2010-09-26
      321     |    1     |   X    |     0.2      |  2012-02-21
      321     |    1     |   Y    |     0.2      |  2012-02-21
      321     |    1     |   U    |     0.2      |  2012-02-21
      321     |    1     |   P    |     0.2      |  2012-02-21
      321     |    1     |   W    |     0.2      |  2012-02-21
      456     |    2     |   Y    |      1       |  2012-11-01

which I need to convert to a "wide" format, like so.. 

        Date     | PersonId | SeqId | Fund1 | Fund2 | Fund3 | Fund4 | Fund5 | Dbu1 | Dbu2 | Dbu3 | Dbu4 | Dbu5  
    ----------------------------------------------------------------------------------------------------------
     2012-04-03  |   456    |   1   |   B   |   F   |   H   |   Z   |   .   |  0.1 |  0.5 |  0.3 |  0.1 |   .   
     2012-03-23  |    8     |   1   |   B   |   A   |   .   |   .   |   .   |  0.5 |  0.5 |   .  |   .  |   .    
     2012-03-24  |    8     |   2   |   C   |   X   |   F   |   .   |   .   |  0.3 |  0.3 |  0.4 |   .  |   .   
     2008-01-01  |   6001   |   1   |   J   |   R   |   .   |   .   |   .   |  0.5 |  0.5 |   .  |   .  |   . 
     2010-09-26  |    76    |   1   |   A   |   B   |   C   |   D   |   .   | 0.25 | 0.25 | 0.25 | 0.25 |   .   
     2010-02-21  |   321    |   1   |   X   |   Y   |   U   |   P   |   W   |  0.2 |  0.2 |  0.2 |  0.2 |  0.2  
     2012-11-01  |   456    |   2   |   Y   |   .   |   .   |   .   |   .   |   1  |   .  |   .  |   .  |   .

Is this possible even though I don't want to aggregate the data in any way?

SQL Fiddle

Upvotes: 2

Views: 3013

Answers (1)

mellamokb
mellamokb

Reputation: 56769

I'm not real good a PIVOT tables, but you can use the following alternative CASE statement pattern to get the output you're looking for:

WITH T AS (
  SELECT
    personid,
    seqid,
    row_number() over (partition BY personid,seqid ORDER BY FundId) AS ROW,
    FundId,
    portfoliodbu,
    date
  FROM
    transactions
)
SELECT
  date,
  personid,
  seqid,
  max(CASE WHEN ROW=1 THEN fundid END) AS fund1,
  max(CASE WHEN ROW=2 THEN fundid END) AS fund2,
  max(CASE WHEN ROW=3 THEN fundid END) AS fund3,
  max(CASE WHEN ROW=4 THEN fundid END) AS fund4,
  max(CASE WHEN ROW=5 THEN fundid END) AS fund5,
  max(CASE WHEN ROW=1 THEN portfoliodbu END) AS dbu1,
  max(CASE WHEN ROW=2 THEN portfoliodbu END) AS dbu2,
  max(CASE WHEN ROW=3 THEN portfoliodbu END) AS dbu3,
  max(CASE WHEN ROW=4 THEN portfoliodbu END) AS dbu4,
  max(CASE WHEN ROW=5 THEN portfoliodbu END) AS dbu5
FROM
  T
GROUP BY
  date,personid,seqid

Demo: SQL Fiddle

Results:

|                             DATE | PERSONID | SEQID | FUND1 |  FUND2 |  FUND3 |  FUND4 |  FUND5 | DBU1 |   DBU2 |   DBU3 |   DBU4 |   DBU5 |
----------------------------------------------------------------------------------------------------------------------------------------------
|   January, 01 2008 00:00:00+0000 |     6001 |     1 |     J |      R | (null) | (null) | (null) |  0.5 |    0.5 | (null) | (null) | (null) |
| September, 26 2010 00:00:00+0000 |       76 |     1 |     A |      B |      C |      D | (null) | 0.25 |   0.25 |   0.25 |   0.25 | (null) |
|     March, 24 2011 00:00:00+0000 |        8 |     2 |     C |      F |      X | (null) | (null) |  0.3 |    0.4 |    0.3 | (null) | (null) |
|  February, 21 2012 00:00:00+0000 |      321 |     1 |     P |      U |      W |      X |      Y |  0.2 |    0.2 |    0.2 |    0.2 |    0.2 |
|     March, 23 2012 00:00:00+0000 |        8 |     1 |     A |      B | (null) | (null) | (null) |  0.5 |    0.5 | (null) | (null) | (null) |
|     April, 03 2012 00:00:00+0000 |      456 |     1 |     B |      F |      H |      Z | (null) |  0.1 |    0.5 |    0.3 |    0.1 | (null) |
|  November, 01 2012 00:00:00+0000 |      456 |     2 |     Y | (null) | (null) | (null) | (null) |    1 | (null) | (null) | (null) | (null) |

Upvotes: 5

Related Questions