Convert Rows to Columns involving Dates

Question

I am a SAS programmer who has basic knowledge of SQL. I am trying to convert SAS programs to SQL scripts and I need help with converting Rows to Columns in SQL Server.

I tried some of the solutions(like PIVOT, Multiple Joins etc.) available in this forum, but could not achieve what I needed. The problem is, the number rows for ID might vary from 1 - 500 (or more) and I need to create these columns dynamically. In SAS however, I do not have to bother about the varying number of rows as SAS will do it automatically using pre-defined procedures/methods (like Proc Transpose or Arrays).

Here is some sample data that I am trying to work with.

Appreciate your help in this regard.

ID  STARTDT ENDDT
1   1/1/2020    1/3/2020
1   2/25/2020   2/28/2020
1   3/10/2020   3/15/2020
2   1/1/2020    1/3/2020
2   2/25/2020   2/28/2020
2   3/10/2020   3/15/2020
2   3/20/2020   3/20/2020
2   3/25/2020   3/31/2020
3   1/1/2020    1/3/2020
4   2/25/2020   2/28/2020
4   3/10/2020   3/15/2020

Desired Output.

ID  STDT1   ENDT1   STDT2   ENDT2   STDT3   ENDT3   STDT4   ENDT4   STDT5   ENDT5 ........... STDT(MAX) ENDT(MAX)
1   1/1/2020    1/3/2020    2/25/2020   2/28/2020   3/10/2020   3/15/2020   
2   1/1/2020    1/3/2020    2/25/2020   2/28/2020   3/10/2020   3/15/2020   3/20/2020   3/20/2020   3/25/2020   3/31/2020
3   1/1/2020    1/3/2020        
4   2/25/2020   2/28/2020   3/10/2020   3/15/2020

[Dynamic pivot](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Nagib Mahfuz, Sep 06 '21 at 06:08
CREATE TABLE #TEMPDATES WITH (DISTRIBUTION = HASH(ID)) AS SELECT ID, STARTDT, ENDDATE, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY STARTDT, ENDDATE) AS RNK FROM MYTABLE GO SELECT A.ID, A.STARTDT AS STDT1, A.ENDDATE AS ENDT1, B.STARTDT AS STDT2, B.ENDDATE AS ENDT2 FROM #TEMPDATES A INNER JOIN #TEMPDATES B ON A.ID = B.ID WHERE A.RNK = 1 AND B.RNK = 2; — Bhavani Kumar Metla, Sep 06 '21 at 08:52
Please provide enough code so others can better understand or reproduce the problem. — Community, Sep 12 '21 at 11:49

Diego Eick Moreira · Accepted Answer · 2021-09-23T07:18:31.153

Here is one possible solution for your problem:

-- create table
CREATE TABLE Dates
(
  ID INT,
  StartDt VARCHAR(20),
  EndDt VARCHAR(20)
)
-- Add sample data
INSERT INTO Dates VALUES (1, '1/1/2020', '1/3/2020')
INSERT INTO Dates VALUES (1, '2/25/2020', '2/28/2020')
INSERT INTO Dates VALUES (1, '3/10/2020', '3/15/2020')
INSERT INTO Dates VALUES (2, '1/1/2020' ,'1/3/2020')
INSERT INTO Dates VALUES (2, '2/25/2020', '2/28/2020')

-- Create a CTE to get the sequence for multiple occurrences of the same ID
-- Sequence1 is used for the second pivot on EndDt
WITH cte AS
(
SELECT
  [ID]
  ,[Sequence] = ROW_NUMBER() OVER(PARTITION BY [ID] ORDER BY [ID])
  ,[Sequence1] = 100 + ROW_NUMBER() OVER(PARTITION BY [ID] ORDER BY [ID])
  ,[StartDt]
  ,[EndDt]
FROM Dates
)
-- SELECY with PIVOT on multiple columns
SELECT
  [ID]
  ,[1] AS startdt1
  ,[101] AS enddt1
  ,[2] AS startdt2
  ,[102] AS enddt2
  ,[3] AS startdt3
  ,[103] AS enddt3
FROM cte x
PIVOT
(
  MAX(StartDt)
  FOR [Sequence] IN ([1], [2], [3])
) p1

PIVOT
(
  MAX(EndDt)
  FOR [Sequence1] IN ([101], [102], [103])
) p2
ORDER BY ID

Reference: https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-ask). — Community, Sep 22 '21 at 08:54

Convert Rows to Columns involving Dates

1 Answers1