With NTILE you are able to split the selection in groups of three. With a CASE statement on this NTILE column you can move the specific rows to columns. The only thing left is to combine alle the results into less rows by removing the NULL values. I currently don't have time to continue qith this . Maybe someone else can pick it up where I left...

if object_id('tempdb..#Weekdaystst') IS NOT NULL

DROP table #Weekdaystst

Create Table #Weekdaystst(

ID nvarchar(3) Unique,

Column1 varchar(50),

Column2 varchar(50)

)

Insert INTO #Weekdaystst Values('OM', 'One,','Monday'),

('TT' ,'Two','Tuesday'),

('TW', 'Three','Wednesday'),

('FT' ,'Four','Thursday'),

('FF', 'Five','Friday'),

('SS', 'Six','Saturday'),

('SU', 'Seven','Sunday')

;with cte_tile as

(select ntile(3) OVER (ORDER BY ID) as tile

, ID

, Column1

, Column2

from #Weekdaystst)

select case when tile = 1 then ID else NULL end as 'ID'

, case when tile = 1 then Column1 else NULL end as 'Column1'

, case when tile = 1 then Column2 else NULL end as 'Column2'

, case when tile = 2 then ID else NULL end as 'Column3'

, case when tile = 2 then Column1 else NULL end as 'Column4'

, case when tile = 2 then Column2 else NULL end as 'Column5'

, case when tile = 3 then ID else NULL end as 'Column6'

, case when tile = 3 then Column1 else NULL end as 'Column7'

, case when tile = 3 then Column2 else NULL end as 'Column8'

from cte_tile

if object_id('tempdb..#Weekdaystst') IS NOT NULL

DROP table #Weekdaystst

Btw.: I have added an additional seventh row to the sample data. I don't know if your actual data always can be grouped in three rows.