SQL Server Combine multiple rows to one row with multiple columns

Question

I have a database in which I have the following rows:

  ID  |  Date start  |  Date end
----------------------------------
  a   |  01-01-1950  |  30-01-1951
  a   |  01-01-1948  |  31-12-1949
  a   |  31-01-1951  |  01-06-2000
  b   |  01-01-1980  |  01-08-2010
  c   |  01-01-1990  |  31-12-2017
  c   |  31-01-1985  |  31-12-1989

What I got

Multiple rows per person
One start and end date per row
In a not chronological order

Select query which I want to return the following:

  ID  |  Date start 1 |  Date end 1  |  Date start 2 |  Date end 2  |  Date start 3 |  Date end 3
---------------------------------------------------------------------------------------------------
  a   |  01-01-1948   |  31-12-1949  |  01-01-1950   |  30-01-1951  |  31-01-1951   |  01-06-2000
  b   |  01-01-1980   |  01-08-2010
  c   |  31-01-1985   |  31-12-1989  |  01-01-1990   |  31-12-2017

What I want:

One row per person
Multiple start and end dates per row
In a chronological order

Most things I was able to find wanted it in the same column, or wouldn't want it sorted on chronological order, so unfortunately those situations didn't apply to me.

I really have now clue how to solve this.

How many Start/End dates you can possibly have for a person? — M.Ali, Jan 04 '17 at 13:47
Seems to me this could be accomplished with a dynamic pivot statement [example](http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) and a window function row_number with column names being Date Start/date end concat rowNumber. `Row_number() over (partition by ID, order by ID, [Date Start])` use that to generate a row number on which you could pivot the dates. — xQbert, Jan 04 '17 at 13:54
What is your presentation/reporting tool that you are using? You should be able to easily format the data in that from your original source table. — iamdave, Jan 04 '17 at 13:58
To clear some things up. A person can have up to 12 dates (it's residence periods between different countries), and I want to export the final result to an Excel to import somewhere else. That's why the column order should be right, otherwise the import wouldn't work well. — Grafit, Jan 04 '17 at 14:15

score 4 · Accepted Answer · answered Jan 04 '17 at 13:58

If you have only three dates, then pivot/conditional aggregation should be fine:

select id,
       max(case when seqnum = 1 then dstart end) as start_1,
       max(case when seqnum = 1 then dend end) as end_1,
       max(case when seqnum = 2 then dstart end) as start_2,
       max(case when seqnum = 2 then dend end) as end_2,
       max(case when seqnum = 3 then dstart end) as start_3,
       max(case when seqnum = 3 then dend end) as end_3
from (select t.*,
             row_number() over (partition by id order by dstart) as seqnum
      from t
     ) t
group by id;

Note: You have to specify the number of columns in the output. If you don't know how many there are, you can either:

Generate a dynamic SQL statement to do the count in advance.
Manually count yourself and add the appropriate columns.

@Grafit . . . Yes, you just need to extend the `select`. – Gordon Linoff Jan 04 '17 at 14:39 — Gordon Linoff, Jan 04 '17 at 14:39

score 2 · Answer 2 · answered Jan 04 '17 at 14:24

Gordon's conditional aggregation would be my first choice. However, If you need to go DYNAMIC

Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName(concat('Date Start ',RN)) +',' + QuoteName(concat('Date End ',RN)) 
                                    From (Select Distinct RN=Row_Number() over (Partition By ID Order By [Date Start]) 
                                            From YourTable) A  
                                    Order by 1 For XML Path('')),1,1,'') 

Select  @SQL = '
Select [ID],' + @SQL + '
From (
        Select ID,B.*
         From (
                Select *,RN=Row_Number() over (Partition By ID Order By [Date Start]) From YourTable
              ) A
         Cross Apply (Values (concat(''Date Start '',A.RN),A.[Date Start])
                            ,(concat(''Date End '',A.RN),A.[Date End]) ) B (Col,Value)
     ) A
 Pivot (max(Value) For [Col] in (' + @SQL + ') ) p'
Exec(@SQL);

Returns

score 0 · Answer 3 · answered Jan 04 '17 at 13:59

Assuming you have a static number of date-ranges, you could achieve this using a window function as follows...

;WITH cteData
    (
    ID,
    DateStart,
    DateEnd
    )
    AS
    (
    SELECT 'a', CONVERT(DATE, '01-01-1950'), CONVERT(DATE, '30-01-1951')
    UNION ALL SELECT 'a', '01-01-1948', '31-12-1949'
    UNION ALL SELECT 'a', '31-01-1951', '01-06-2000'
    UNION ALL SELECT 'b', '01-01-1980', '01-08-2010'
    UNION ALL SELECT 'c', '01-01-1990', '31-12-2017'
    UNION ALL SELECT 'c', '31-01-1985', '31-12-1989'
    ),
    cteDefineColumns
    AS
    (
    SELECT RangeColumnID = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DateStart),
        *
        FROM cteData
    )
    SELECT col1.ID,
        [Date start 1] = col1.DateStart,
        [Date end 1] = col1.DateEnd,
        [Date start 2] = col2.DateStart,
        [Date end 2] = col2.DateEnd,
        [Date start 3] = col3.DateStart,
        [Date end 3] = col3.DateEnd
        FROM cteDefineColumns AS col1
            LEFT OUTER JOIN cteDefineColumns AS col2
                ON col1.ID = col2.ID
                AND col2.RangeColumnID = 2
            LEFT OUTER JOIN cteDefineColumns AS col3
                ON col1.ID = col3.ID
                AND col3.RangeColumnID = 3
        WHERE col1.RangeColumnID = 1
        ORDER BY col1.ID,
            col1.DateStart,
            col1.DateEnd;

score 0 · Answer 4 · answered Jan 04 '17 at 14:00

I'm not sure why do you neet it, because this structure is not too good.

if object_Id('tempdb..#TmpRankedTable') is not null drop table #TmpRankedTable
select Id, strt_dt, end_dt, row_number() over(partition by Id order by strt_dt) OrbyCol
into #TmpRankedTable
from dbo.YourTable

if object_Id('tempdb..#TmpStarts') is not null drop table #TmpStarts
select *
into #TmpStarts
from (
    select Id, strt_dt, OrbyCol
    from #TmpRankedTable) t
pivot (min(strt_dt) for OrbyCol in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) p

if object_Id('tempdb..#TmpEnds') is not null drop table #TmpEnds
select *
into #TmpEnds
from (
    select Id, end_dt, OrbyCol
    from #TmpRankedTable) t
pivot (min(end_dt) for OrbyCol in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) p

select 
    s.Id, 
    s.[1] [Start 1], 
    e.[1] [End 1], 
    s.[2] [Start 2], 
    e.[2] [End 2],
    s.[3] [Start 3],
    e.[3] [End 3], 
    s.[4] [Start 4],
    e.[4] [End 4],
    s.[5] [Start 5],
    e.[5] [End 5],
    s.[6] [Start 6], 
    e.[6] [End 6],
    s.[7] [Start 7], 
    e.[7] [End 7],
    s.[8] [Start 8], 
    e.[8] [End 8],
    s.[9] [Start 9], 
    e.[9] [End 9],
    s.[10] [Start 10], 
    e.[10] [End 10]
from #TmpStarts s
inner join #TmpEnds e on s.Id = e.Id

score 0 · Answer 5 · answered Jan 04 '17 at 14:28

You can use pivot to transpose and query as below:

;with cte as (
select *, RowN = row_number() over(partition by id order by datestart) from #temp ) 
, cte2 as (
    select id, [1] as [datestart1], [2] as [datestart2], [3] as datestart3 from 
    (select id, datestart, RowN from cte) sourcetable
    pivot (max(datestart) for RowN in ([1],[2],[3]) ) p
) 
, cte3 as (
    select id, [1] as [dateend1], [2] as [dateend2], [3] as dateend3 from 
    (select id, dateend, RowN from cte) sourcetable
    pivot (max(dateend) for RowN in ([1],[2],[3]) ) p
) select c2.id, c2.datestart1,c3.dateend1,c2.datestart2,c3.dateend2,c2.datestart3,c3.dateend3 
    from cte2 c2 left join cte3 c3 on c2.id = c3.id

If you have dynamic columns you can create dynamic query using stuff to create list of columns and run this query as dynamic query to get all list of columns

SQL Server Combine multiple rows to one row with multiple columns

5 Answers5

Linked