none
How to re-arrange records in columns RRS feed

  • Question

  • Hello community,

    I hope you're all enjoying the festive season, and having a nice time with your family and friends

    I have table which currently looks like the following:

    Can someone please help with SQL code that will change the table to appear as follows:

    The table structure is as follows:

    CREATE TABLE neighbourhoods3.dbo.timeslots (
     year VARCHAR(50) NULL
     ,time_of_day VARCHAR(50) NULL
     ,day_of_week VARCHAR(50) NULL
     ,month VARCHAR(50) NULL
     ,season VARCHAR(50) NULL
     ,public_holiday VARCHAR(50) NULL
     ,public_holiday_minus_1 VARCHAR(50) NULL
    ) ON [PRIMARY]
    GO

    Any help greatly appreciated.

    Cheers


    carlton

    Friday, December 27, 2019 11:41 AM

Answers

  • Check an example for the first three columns:

    CREATE TABLE #timeslots (
     year VARCHAR(50) NULL,
     time_of_day VARCHAR(50) NULL,
     day_of_week VARCHAR(50) NULL
    ) ON [PRIMARY]
    
    
    insert into #timeslots ([year], time_of_day, day_of_week) values 
    	('2018', 'Noon', 'Monday'),
    	('2019,2019', 'Afternoon,Afternoon', 'Tuesday,Tuesday'),
    	('2020,2021,2021', 'Morning,Afternoon,Night', 'Monday,Sunday,Sunday')
    
    select * from #timeslots
    
    ;
    with Q0 as
    (
    	select *, ROW_NUMBER() over (order by year) as rn
    	from #timeslots
    ),
    Q1 as
    (
    	select distinct txt
    	from #timeslots
    	unpivot ( txt for y in ([year], [time_of_day], [day_of_week]) ) as u
    ),
    Q2 as 
    (
    	select txt, 0 as i, 0 as b, charindex(',', txt) as e
    	from Q1
    	union all
    	select txt, i+1 as i, e+1 as b, charindex(',', txt, e+1) as e
    	from Q2
    	where e > 0
    ),
    Q3 as
    (
    	select txt, i, substring(txt, b, case when e > 0 then e-b else 50 end ) as v
    	from Q2
    )
    merge into Q0 as X
    using Q0 as S
    	inner join Q3 as y on y.txt = S.[year]
    	left join Q3 as t on t.txt = S.time_of_day and t.i = y.i
    	left join Q3 as d on d.txt = S.day_of_week and d.i = y.i
    on S.rn = X.rn and y.i = 0
    when matched 
    then 
    	update 
    	set 
    		[year] = y.v,
    		time_of_day = t.v,
    		day_of_week = d.v
    when not matched
    then
    	insert ([year], time_of_day, day_of_week) values
    	(y.v, t.v, d.v)
    ;
    
    select * from #timeslots
    order by [year]
    
    
    drop table #timeslots
    

     

    It assumes that the number of values in each row corresponds to the number from year column.

    Can be extended for more columns.

    • Marked as answer by cpatte7372 Saturday, December 28, 2019 10:25 AM
    Friday, December 27, 2019 7:52 PM

All replies

  • Hi,

    I recommend you to refer this link https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns . Most likely your question is already answered here.


    Thank you
    Hemantgiri S. Goswami | SQL Server Consultant
    SQL Server Citation | Twitter | Facebook
    Author of book - SQL Server 2008 High Availability

    Friday, December 27, 2019 11:47 AM
    Moderator
  • Hemantgiri, thanks for reaching out. 

    I checked the link - TBH, I thought it would be much easier than the solution described on stack overflow.

    Anyhow, when I attempted to apply the string_split function I get the following error

    'STRING_SPLIT' is not a recognized built-in function name.

    I'm running SQL Server Express 14.0

    Do I need to running an updated version of SQL Server to make this function work?


    carlton

    Friday, December 27, 2019 12:01 PM
  • Do you expect various number of values (more than two)?

    Friday, December 27, 2019 12:06 PM
  • Hi Viorel,

    I do expect more than two, but I thought if someone could help me with this, I can sort out the rest


    carlton

    Friday, December 27, 2019 1:01 PM
  • The string_split was introduced in SQL Server 2016. You can use it in 2016, 2017 or 2019 databases.

    But you need to set your database compatibility level to 130 or above in order to use string_split function.

    Friday, December 27, 2019 4:15 PM
    Moderator
  • By the way, please redesign your table and your query will be way easy to write. 

    The table you have now is not a good one.

    Friday, December 27, 2019 5:11 PM
    Moderator
  • Check an example for the first three columns:

    CREATE TABLE #timeslots (
     year VARCHAR(50) NULL,
     time_of_day VARCHAR(50) NULL,
     day_of_week VARCHAR(50) NULL
    ) ON [PRIMARY]
    
    
    insert into #timeslots ([year], time_of_day, day_of_week) values 
    	('2018', 'Noon', 'Monday'),
    	('2019,2019', 'Afternoon,Afternoon', 'Tuesday,Tuesday'),
    	('2020,2021,2021', 'Morning,Afternoon,Night', 'Monday,Sunday,Sunday')
    
    select * from #timeslots
    
    ;
    with Q0 as
    (
    	select *, ROW_NUMBER() over (order by year) as rn
    	from #timeslots
    ),
    Q1 as
    (
    	select distinct txt
    	from #timeslots
    	unpivot ( txt for y in ([year], [time_of_day], [day_of_week]) ) as u
    ),
    Q2 as 
    (
    	select txt, 0 as i, 0 as b, charindex(',', txt) as e
    	from Q1
    	union all
    	select txt, i+1 as i, e+1 as b, charindex(',', txt, e+1) as e
    	from Q2
    	where e > 0
    ),
    Q3 as
    (
    	select txt, i, substring(txt, b, case when e > 0 then e-b else 50 end ) as v
    	from Q2
    )
    merge into Q0 as X
    using Q0 as S
    	inner join Q3 as y on y.txt = S.[year]
    	left join Q3 as t on t.txt = S.time_of_day and t.i = y.i
    	left join Q3 as d on d.txt = S.day_of_week and d.i = y.i
    on S.rn = X.rn and y.i = 0
    when matched 
    then 
    	update 
    	set 
    		[year] = y.v,
    		time_of_day = t.v,
    		day_of_week = d.v
    when not matched
    then
    	insert ([year], time_of_day, day_of_week) values
    	(y.v, t.v, d.v)
    ;
    
    select * from #timeslots
    order by [year]
    
    
    drop table #timeslots
    

     

    It assumes that the number of values in each row corresponds to the number from year column.

    Can be extended for more columns.

    • Marked as answer by cpatte7372 Saturday, December 28, 2019 10:25 AM
    Friday, December 27, 2019 7:52 PM
  • Hi Viorel,

    This is amazing. It worked like a dream.


    carlton

    Saturday, December 28, 2019 10:25 AM