CODEWITHSUNDEEP
sql-server
codeaf
codeaf

Reputation: 81

Select rows from a table in groups of 3 as a single row

Let's say that I have a table as such:

Row       Title       Date
----      -----       ----
1         Title 1     1/1/2018
2         Title 2     1/2/2018
3         Title 3     1/3/2018
4         Title 4     1/4/2018
5         Title 5     1/5/2018
6         Title 6     1/6/2018

I would like to select the data into rows that contain three rows per one row (if that makes sense). For example like this

Row1   Title1   Date1      Row2   Title2   Date2      Row3   Title3   Date3
-------------------------------------------------------------------------------
1      Title 1  1/1/2018   2      Title 2  1/2/2018   3      Title 3  1/3/2018
4      Title 4  1/4/2018   5      Title 5  1/5/2018   6      Title 6  1/6/2018

Is this possible? I've tried using a temp table but was unsuccessful :/

Upvotes: 0

Views: 45

Answers (1)

BoCoKeith
BoCoKeith

Reputation: 946

WITH
    numbered AS (
        SELECT  Row ,
                Title ,
                Date ,
                ROW_NUMBER() OVER (ORDER BY Row) AS RowNumber
        FROM    Table
    )
SELECT  n1.Row AS Row1 ,
        n1.Title AS Title1 ,
        n1.Date AS Date1 ,
        n2.Row AS Row2 ,
        n2.Title AS Title2 ,
        n2.Date AS Date2 ,
        n3.Row AS Row3 ,
        n3.Title AS Title3 ,
        n3.Date AS Date3
FROM    numbered n1
LEFT OUTER JOIN numbered n2 ON n2.RowNumber = n1.RowNumber + 1
LEFT OUTER JOIN numbered n3 ON n3.RowNumber = n1.RowNumber + 2
WHERE   (n1.RowNumber - 1) % 3 = 0

Upvotes: 1

Related Questions