October 24, 2016 at 6:59 am
I currently have a simple select query
Select ID,Column1,column2
from Weekdaystst
Output :
ID Column1 Column2
OM One Monday
TT Two Tuesday
TW Three Wednesday
FT Four Thursday
FF Five Friday
SS Six Saturday
I would like the desired ouput to be that each row takes 3 unique ID columns before creating a second row
Desired output
IDColumn1Column2Column3Column 4Column 5Column 6Column 7Column 8
OMOne MondayTT Two TuesdayTT ThreeWednesday
FTFour ThursdayFF Five FridaySS Six Saturday
These are the statements to recreate the table
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')
October 24, 2016 at 7:42 am
There's no way to order the data as it is, so there's no way to ensure a consistent ordered result without hard coding values.
October 24, 2016 at 7:52 am
Hi Luis
The ordering is not important and if it be I will set in in an ORDER BY clause.For now though I am interested in how i could get it to display that way in no particular order
October 24, 2016 at 8:24 am
Here's an option. It seems a bit odd this requirement. The ORDER BY in the ROW_NUMBER function should probably be changed.
SELECT
MAX( CASE WHEN rn % 3 = 0 THEN ID END) AS ID
,MAX( CASE WHEN rn % 3 = 0 THEN Column1 END) AS Column1
,MAX( CASE WHEN rn % 3 = 0 THEN Column2 END) AS Column2
,MAX( CASE WHEN rn % 3 = 1 THEN ID END) AS Column3
,MAX( CASE WHEN rn % 3 = 1 THEN Column1 END) AS Column4
,MAX( CASE WHEN rn % 3 = 1 THEN Column2 END) AS Column5
,MAX( CASE WHEN rn % 3 = 2 THEN ID END) AS Column6
,MAX( CASE WHEN rn % 3 = 2 THEN Column1 END) AS Column7
,MAX( CASE WHEN rn % 3 = 2 THEN Column2 END) AS Column8
FROM(
SELECT *, (ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1) rn
FROM Weekdaystst)x
GROUP BY rn / 3;
October 24, 2016 at 8:34 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply