multiple row values to same row and different columns

Question

I have a table with 4 fields Person_id, Store_id, startdate and enddate. For a particular value for person_id, there can be a number of records with different start and end dates. Here I need to update the multiple row values to same row and different columns.

Example 1:

Person_id        Store_ID         Startdate       enddate
10000351067      10000232561      2016-09-09      2016-09-16
10000351067      10000232561      2016-09-16      2016-10-03
10000351067      10000232561      2016-10-03      2016-10-07
10000351067      10000232561      2016-10-07      2017-01-17
10000351067      10000232561      2017-01-17      2018-04-05
10000351067      10000232561      2018-04-05      NULL

Example 2:

10000193858      10000225875      2016-07-13          2016-08-03
10000193858      10000225875      2016-08-03          2017-05-17
10000193858      10000225875      2017-05-17          2017-06-05
10000193858      10000225875      2017-05-31          2017-06-05
10000193858      10000225875      2017-06-05          2017-06-13
10000193858      10000225875      2017-06-13          2017-08-16
10000193858      10000225875      2017-08-07          2017-08-16
10000193858      10000225875      2017-08-16          2017-08-18
10000193858      10000225875      2017-08-18          2017-08-31
10000193858      10000225875      2017-08-31          2018-01-05
10000193858      10000225875      2018-01-05          NULL

Result set:

For example1:

Person_id   store_id      Start_date_1 Ended_at_1  Started_at_2 Ended_at_2...
10000351067 10000232561   2016-09-09   2016-09-16  2016-09-16   2016-10-03...

For example2:

Person_id    store_id     Start_date_1 Ended_at_1  Started_at_2 Ended_at_2...
10000193858  10000225875  2016-07-13   2016-08-03  2016-08-03   2017-05-17....

What database? Also, can there be an unlimited number of start/end dates for a person_id? — Shawn
– Shawn, Commented Apr 24, 2018 at 19:34
@Shawn, currently I'm using Microsoft SQL server version 2008. Yes, there may be unlimited number of start-dates and end-dates for person_id's. Thanks! — red
– red, Commented Apr 24, 2018 at 19:36
Normally, a PIVOT would work, but if there can be unlimited columns, how are you going to consume that data? How will you know that Person_ID has Start_date_9999999? — Shawn
– Shawn, Commented Apr 24, 2018 at 19:40
2008 or 2008R2? There may be subtle differences in solutions. — Shawn
– Shawn, Commented Apr 24, 2018 at 19:42
@shawn, Thanks for responding! Highest columns that may have is max 20. — red
– red, Commented Apr 24, 2018 at 19:43

Shawn · Accepted Answer · 2018-04-24 22:36:02Z

This is a somewhat convoluted issue, but can be resolved with a CROSS APPLY to unpivot data, followed by a PIVOT to get it back in the format you need. It was tested on SQL 2017, but I believe PIVOT and CTEs were added in 2005 and ROW_NUMBER() in 2008, so should work in your version of SQL. In the main query, I aliased the columns so you could see where the data was coming from. Since you said you can have 20 start/endDate columns, I added those in, but if needed, you can use dynamic SQL to make a more dynamic list.

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE t1 ( Person_id bigint, Store_ID bigint, Startdate date, enddate date ) ;
INSERT INTO t1 (Person_id,Store_ID,Startdate,enddate)
VALUES 
    ( 10000351067,10000232561,'2016-09-09','2016-09-16')
  , ( 10000351067,10000232561,'2016-09-16','2016-10-03')
  , ( 10000351067,10000232561,'2016-10-03','2016-10-07')
  , ( 10000351067,10000232561,'2016-10-07','2017-01-17')
  , ( 10000351067,10000232561,'2017-01-17','2018-04-05')
  , ( 10000351067,10000232561,'2018-04-05',NULL)
  , ( 10000193858,10000225875,'2016-07-13','2016-08-03')
  , ( 10000193858,10000225875,'2016-08-03','2017-05-17')
  , ( 10000193858,10000225875,'2017-05-17','2017-06-05')
  , ( 10000193858,10000225875,'2017-05-31','2017-06-05')
  , ( 10000193858,10000225875,'2017-06-05','2017-06-13')
  , ( 10000193858,10000225875,'2017-06-13','2017-08-16')
  , ( 10000193858,10000225875,'2017-08-07','2017-08-16')
  , ( 10000193858,10000225875,'2017-08-16','2017-08-18')
  , ( 10000193858,10000225875,'2017-08-18','2017-08-31')
  , ( 10000193858,10000225875,'2017-08-31','2018-01-05')
  , ( 10000193858,10000225875,'2018-01-05',NULL)
;

Query 1:

/* Start with a CTE to get the base data, plus the row number for the pivot colName */
; WITH cte_details AS (
  SELECT t1.Person_ID
    , t1.Store_ID
    , t1.StartDate
    , t1.EndDate
    , ROW_NUMBER() OVER ( PARTITION BY t1.Person_ID, t1.Store_ID ORDER BY t1.StartDate ) AS rn
  FROM t1
)
SELECT pvt.* 
FROM (
  /* Unpivot values, using a CROSS APPLY */
  SELECT cd.Person_ID, cd.Store_ID
    , crs.c + CAST(cd.rn AS varchar(5)) AS colName
    , crs.v AS colValue
  FROM cte_details cd
  CROSS APPLY (
    SELECT 'startDate', startDate UNION ALL
    SELECT 'endDate', endDate  
  ) crs (c, v)
) s1
/* Now PIVOT those back to get the results. */
PIVOT (
  max(s1.colValue) 
  FOR s1.colName IN (
        startDate1, endDate1, startDate2, endDate2, startDate3, endDate3
      , startDate4, endDate4, startDate5, endDate5, startDate6, endDate6
      , startDate7, endDate7, startDate8, endDate8, startDate9, endDate9
      , startDate10, endDate10, startDate11, endDate11, startDate12, endDate12
      , startDate13, endDate13, startDate14, endDate14, startDate15, endDate15
      , startDate16, endDate16, startDate17, endDate17, startDate18, endDate18
      , startDate19, endDate19, startDate20, endDate20      
   )
) pvt

Results:

|   Person_ID |    Store_ID | startDate1 |   endDate1 | startDate2 |   endDate2 | startDate3 |   endDate3 | startDate4 |   endDate4 | startDate5 |   endDate5 | startDate6 |   endDate6 | startDate7 |   endDate7 | startDate8 |   endDate8 | startDate9 |   endDate9 | startDate10 |  endDate10 | startDate11 | endDate11 | startDate12 | endDate12 | startDate13 | endDate13 | startDate14 | endDate14 | startDate15 | endDate15 | startDate16 | endDate16 | startDate17 | endDate17 | startDate18 | endDate18 | startDate19 | endDate19 | startDate20 | endDate20 |
|-------------|-------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|-------------|------------|-------------|-----------|-------------|-----------|-------------|-----------|-------------|-----------|-------------|-----------|-------------|-----------|-------------|-----------|-------------|-----------|-------------|-----------|-------------|-----------|
| 10000193858 | 10000225875 | 2016-07-13 | 2016-08-03 | 2016-08-03 | 2017-05-17 | 2017-05-17 | 2017-06-05 | 2017-05-31 | 2017-06-05 | 2017-06-05 | 2017-06-13 | 2017-06-13 | 2017-08-16 | 2017-08-07 | 2017-08-16 | 2017-08-16 | 2017-08-18 | 2017-08-18 | 2017-08-31 |  2017-08-31 | 2018-01-05 |  2018-01-05 |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |
| 10000351067 | 10000232561 | 2016-09-09 | 2016-09-16 | 2016-09-16 | 2016-10-03 | 2016-10-03 | 2016-10-07 | 2016-10-07 | 2017-01-17 | 2017-01-17 | 2018-04-05 | 2018-04-05 |     (null) |     (null) |     (null) |     (null) |     (null) |     (null) |     (null) |      (null) |     (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |

https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms177410(v=sql.105)

Steven Black · Accepted Answer · 2018-04-24 19:50:20Z

1

if I understand correctly you want to UPDATE some fields WHERE certain conditions hold

UPDATE 
SET Store_ID=10000232561, Startdate=2016-09-09, enddate=2016-09-16
WHERE Person_id = 10000351067

the documentation for microsoft's T-SQL is pretty good, see the update statement docs

answered Apr 24, 2018 at 19:50

Steven Black

2,2521 gold badge19 silver badges28 bronze badges

Collectives™ on Stack Overflow

multiple row values to same row and different columns

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related