Transposing rows into columns based on a condition sql

Question

I have a following table (a simplified example, in fact the table contains multiple IDs, with variable numbers of dates, and variable number of events for each date):

IDs   Date                  Event
102   1996-10-16 00:00:00   A
102   1996-10-23 00:00:00   A
102   1996-10-23 00:00:00   B
102   1997-01-14 00:00:00   A
103   1997-01-14 00:00:00   D
103   1997-01-15 00:00:00   A
103   1997-01-16 00:00:00   A
103   1997-01-16 00:00:00   B
103   1997-01-16 00:00:00   C

I am trying to get a table where I will have distinct IDs/Date pairs, with the rows for which there have been multiple events recoded being transposed into columns. So, I'm looking for a table which for this example would look like this:

IDs   Date                  Event   Event2   Event3
102   1996-10-16 00:00:00   A       NULL     NULL
102   1996-10-23 00:00:00   A       B        NULL
102   1997-01-14 00:00:00   A       NULL     NULL
103   1997-01-14 00:00:00   D       NULL     NULL
103   1997-01-15 00:00:00   A       NULL     NULL
103   1997-01-16 00:00:00   A       B        C

I'm sorry for not posting any code, but I frankly don't even know how to start with this.

score 4 · Accepted Answer · edited May 23 '17 at 12:19

Details about PIVOT method.

And helpfull answers:

Using PIVOT in SQL Server 2008

MSSQL dynamic pivot column values to column header

Try this code:

-- Temporary table...
create table ##myTable (
        IDs int
        ,[Date] datetime
        ,[Event] varchar(1)
        )

-- ... with sample data
insert ##myTable
        select 102, '2010-01-01', 'A'
union   select 102, '2010-01-01', 'B'
union   select 102, '2010-01-01', 'C'
union   select 102, '2010-01-01', 'E'
union   select 103, '2010-01-01', 'A'
union   select 104, '2010-01-01', 'B'
union   select 104, '2010-01-01', 'C'
union   select 105, '2010-01-01', 'F'

-- Variables
DECLARE @cols   AS NVARCHAR(MAX)
        ,@query AS NVARCHAR(MAX)

-- Build column name for our result.
-- The ROW_NUMBER() operator gives us the rank of the event for
-- the combination of IDs and Date. With that, event B for IDs 104
-- will have rank 1, and then will appear in the 1st column.
SELECT  @cols = STUFF(
                       (SELECT  DISTINCT
                        ',' + QUOTENAME('Event' + LTRIM(STR(
                                    ROW_NUMBER() OVER (
                                        PARTITION BY IDs, [Date]
                                        ORDER BY IDs, [Date]
                                    )
                                )))
                        FROM    ##myTable
                        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
                    , 1, 1, '')

set @query = '
    SELECT  IDs, [Date], ' + @cols + '
    FROM    (
            SELECT  IDs
                    ,[Date]
                    ,[Event]
                    ,''Event'' + LTRIM(STR(
                            ROW_NUMBER() OVER (
                                PARTITION BY IDs, [Date]
                                ORDER BY IDs, [Date]
                            )
                    )) as [EventNo]
            FROM    ##myTable
            ) x
    PIVOT
            (
            MAX([Event])
            FOR [EventNo] IN (' + @cols + ')
            ) p'

execute sp_executesql @query

-- Remove temporary table
drop table ##myTable

And the result :

enter image description here

Thank you for the suggestion. I'll admit that it is far beyond my current level of sql knowledge... But I do have a question. It produces a column for each of the events possible. So, if I have 100 distinct events in the whole dataset, it will produce a 100 columns, with most animals having only a few entries. Is it not possible to havee a similar table, but instead of having a particular event type as a column, rather have "Event1", "Event2" etc? This would save on space quite considerably! — branwen85, Feb 24 '15 at 17:40
Well, maybe could you refine your question with other examples. Do you want to see Event A and Event B in the same column "Event 1" on different rows (some sort of reducing the result to useful columns), or do you want normalized column names (or both) ? — Rubik, Feb 24 '15 at 18:07
I have now edited the question, hopefully it will show better what I'm after. The total number of Events columns should be the maximum of events recorded for a given IDs/Date pair, not the number of distinct events recoded in the data. So, in this example, I have 4 distinct events that have been recorded, but only 3 were recorded on the same day for the same individual, so the number of columns I'm after is 3. — branwen85, Feb 26 '15 at 09:38
This is exactly what I was looking for! Thank you! Just an additional question, or two. First, the order of columns is random, i.e. I get Event14, Event2, Event10 etc... This is a cosmetic thing, but I just wanted to check that it's not an indication of a problem? Second, I have never used syntax like this, and I'm a bit puzzled where I should put the "into" statement when I want to save the table... — branwen85, Feb 27 '15 at 10:39

score 1 · Answer 2 · answered Feb 24 '15 at 12:54

1

If you only have two events, you can do this with min(), max(), and some additional logic:

select ids, date, min(event) as event,
       (case when min(event) <> max(event) then max(event) end) as event2
from table t
group by ids, date;

This is standard SQL so it should work in any database.

answered Feb 24 '15 at 12:54

Gordon Linoff

1,242,037
58
646
786

Unfortunately, the numbers of events will vary quite considerably. Most ID/dates will have just 1, but there might be some ID/Date pairs which will have up to 10 events. – branwen85 Feb 24 '15 at 12:56

score 0 · Answer 3 · answered Feb 24 '15 at 13:19

try this first select ids and then fetch all column based on above id ....

<?php
    $query = $database->getRows("SELECT DISTINCT ids FROM table");  
    ?>

    <table>
    <?php foreach($query as $row){ ?>
            <tr>

                <td><?php echo $row['ids']; ?></td>

            <?php
            $id= $row["ids"];
            $sub_inner = $database->getRows("SELECT date,Event,Event2 FROM table where ids= :ids",
            array(':ids'=>$id));                        
            ?>


    <td><?php foreach($sub_inner as $list){ ?><?php echo $list['date']; ?></td><td><?php echo $list['Event']; ?><?php } ?></td> 
    <td><?php echo $list['Event2']; ?></td> 

    <?php } ?>

Transposing rows into columns based on a condition sql

3 Answers3

Linked