SQL Server PIVOT help for dates

Question

I am having a table that is having data dynamic. A general example

A               B                    C
04/05/2020     04/05/2020 9:00     04/05/2020 13:00
04/05/2020     04/05/2020 13:30     04/05/2020 17:05

I need to have the data in the same row like

A               B                    C                   D                 E
04/05/2020     04/05/2020 9:00     04/05/2020 13:00  04/05/2020 13:30    04/05/2020 17:05

I tried to use PIVOT but that didn't helped me out any suggestions?

Answer 1

You first should get all your data on a single dataset:

with data as (
  select A as value from MyTable
  union 
  select B from MyTable
  union 
  select C from MyTable
)
select value from data order by value

Once you have normalized your data, then you can use a generic solution for converting rows to columns.

Like this one : Efficiently convert rows to columns in sql server

It would look like :

-- Create the sample data

create table MyTable (A datetime, B datetime, C datetime)
insert into MyTable (A, B, C)
            values ('04/05/2020', '04/05/2020 9:00', '04/05/2020 13:00'),
                   ('04/05/2020', '04/05/2020 13:30', '04/05/2020 17:05')

-- Normalize the data
;
with data as (
  select A as value from MyTable
  union 
  select B from MyTable
  union 
  select C from MyTable
)
select value 
into #data       
from data 
order by value

-- Return rows as columns

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

select @cols = STUFF((SELECT ',' + QUOTENAME(value) 
                    from #data
                    order by value
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select value
                from #data
            ) x
            pivot 
            (
                max(value)
                for value in (' + @cols + N')
            ) p '

exec sp_executesql @query;

drop table #data

Answer 2

Basically, what Marc said. Here is an example of what it should look like. The though part which is described in Marcs Link is how to make this dynamic as for the PIVOT function, the values need to be defined initially and cannot be dynamic:

CREATE TABLE dbo.Test(
  A VARCHAR(10),
  B VARCHAR(10),
  C VARCHAR(10)
)

INSERT INTO [dbo].[Test]
       ( [A]
        ,[B]
        ,[C]
       )
VALUES
(
  'A' -- A - VARCHAR
 ,'B' -- B - VARCHAR
 ,'C' -- C - VARCHAR
),
(
  'A' -- A - VARCHAR
 ,'B' -- B - VARCHAR
 ,'C' -- C - VARCHAR
);


SELECT [1], [2], [3], [4], [5], [6], [7], [8], [9]
FROM (
   SELECT *, ROW_NUMBER() OVER (ORDER BY A) AS Id 
     FROM (
     SELECT A, 'Col' AS X FROM dbo.Test
     UNION ALL
     SELECT B, 'Col' AS X FROM dbo.Test
     UNION ALL
     SELECT C, 'Col' AS X FROM dbo.[Test] tes
     ) AS Q
    ) AS P
PIVOT (MAX(A) FOR Id IN ([1], [2], [3], [4], [5], [6], [7], [8], [9])) AS Pvt