CODEWITHSUNDEEP
sqlcommon-table-expressionsql-server-2012
Mcphee78
Mcphee78

Reputation: 3

Combine multi rows into one but have new row based on value

I want to combine multi rows into one but have a new row when a certain value appears in a field see example data below

how data is currently in table

IncomingNumber  QueNumber Datetime
--------------  --------- -------------------
12345678        1         2012-01-01 09:01:00
12345678        2         2012-01-01 09:02:00
12345678        3         2012-01-01 09:05:00
12345678        2         2012-01-01 09:07:00
12345678        3         2012-01-01 09:08:00
12345678        1         2012-01-01 09:10:00
12345678        2         2012-01-01 09:11:00
12345678        3         2012-01-01 09:13:00
09876543        1         2012-01-01 09:01:00
09876543        2         2012-01-01 09:02:00
09876543        1         2012-01-01 09:05:00
09876543        2         2012-01-01 09:06:00
09876543        3         2012-01-01 09:08:00

how I want the data to look like

IncomingNumber  Datetime1            Datetime2            Datetime3            Datetime4            Datetime5
--------------  -------------------  -------------------  -------------------  -------------------  -------------------
12345678        2012-01-01 09:01:00  2012-01-01 09:02:00  2012-01-01 09:05:00  2012-01-01 09:07:00  2012-01-01 09:08:00
12345678        2012-01-01 09:10:00  2012-01-01 09:11:00  2012-01-01 09:13:00  Null                 Null
09876543        2012-01-01 09:01:00  2012-01-01 09:02:00  Null                 Null                 Null
09876543        2012-01-01 09:05:00  2012-01-01 09:06:00  2012-01-01 09:08:00  Null                 Null

So every time a 1 appears in the QueNumber field it's a new record. I know it has something to do with a CTE query but I've never really used them and am totally stuck

Upvotes: 0

Views: 143

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

Welcome to StackOverflow. It usually helps other people if you post DDL and sample data to help them help you. Here's your table data.

create table tbl (IncomingNumber int,QueNumber int,Datetime datetime);
insert tbl values
    (12345678 ,1 ,'2012-01-01 09:01:00'),
    (12345678 ,2 ,'2012-01-01 09:02:00'),
    (12345678 ,3 ,'2012-01-01 09:05:00'),
    (12345678 ,2 ,'2012-01-01 09:07:00'),
    (12345678 ,3 ,'2012-01-01 09:08:00'),
    (12345678 ,1 ,'2012-01-01 09:10:00'),
    (12345678 ,2 ,'2012-01-01 09:11:00'),
    (12345678 ,3 ,'2012-01-01 09:13:00'),
    (09876543 ,1 ,'2012-01-01 09:01:00'),
    (09876543 ,2 ,'2012-01-01 09:02:00'),
    (09876543 ,1 ,'2012-01-01 09:05:00'),
    (09876543 ,2 ,'2012-01-01 09:06:00'),
    (09876543 ,3 ,'2012-01-01 09:08:00');

The following query gives you what you need, up to 5 datetime columns. If any row produces more than 5 columns, the extras won't get shown. I've used a pattern that is easy enough for you to expand upon. Counting from the bottom up, you only need to change the 2nd and 4th lines to cater for more datetime columns.

;with c1 as (
    select *,rn=ROW_NUMBER() over (partition by IncomingNumber order by DateTime)
    from tbl
), c2 as (
    select IncomingNumber,1 row,1 col,rn,DateTime
    from c1
    where rn=1
    union all
    select c1.IncomingNumber,
            case when c1.QueNumber=1 then c2.row+1 else c2.row end,
            case when c1.QueNumber=1 then 1 else c2.col+1 end,
            c1.rn,
            c1.DateTime
    from c2
    join c1 on c1.IncomingNumber=c2.IncomingNumber and c1.rn=c2.rn+1
)
select IncomingNumber,[1][DateTime1],[2][DateTime2],[3][DateTime3],[4][DateTime4],[5][DateTime5]
from(select IncomingNumber,row,col,DateTime from c2)p
pivot(max(DateTime)for col in([1],[2],[3],[4],[5]))v
order by IncomingNumber,row

Upvotes: 3

Related Questions