Select a set number of rows as columns

Question

Is there a simple way to select a set number of rows as columns?

For example.  I would like to include employee names in a query but I want to only display 5 employee names per row.  I need the names to appear horizontal instead of veritical.

output sample

Jane     Bill      Chris     Mike   Joe

Answer 1

Yeah you can do it using PIVOT query which will a better option.Any thing Else ? :)

To know :

http://shivasoft.in/blog/sql/sqlserver/sql-server-pivot-table-example/

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 2

SELECT something,
       MIN(CASE WHEN name = 'Bill' THEN somevalue END) AS Bill,
       MIN(CASE WHEN name = 'Chris' THEN somevalue END) AS Chris,
       ...
FROM   tbl
GROUP  BY something

There is also the PIVOT operator that may give you shorter code, but it is proprietary to SQL Server, while the above runs on any DBMS. It is also very easy to extend and modify, while PIVOT leaves you painted into a corner.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

;with cte as (select Name, row_number() over (order by Name) as Rn from Names table), 

cte2 as (select Name, (Rn+1)/5 as Id, row_number() over (partition by (Rn+1)/5 order by Rn) as NewRn from cte)

select * from cte2 PIVOT (max(name) for NewRn IN ([1],[2],[3],[4],[5])) pvt

The above is from the top of my head, but should give you the idea. We first divide all names by sets of 5 and then numbering them inside each set.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

Here is one way of doing it, using nested CTE-s, GROUP BY, OVER clause and CASE expressions. Not simple, but works.

;WITH cte 
     AS (SELECT lastname, 
                ( ( Row_number() 
                      OVER( 
                        ORDER BY lastname) ) % 5 )     AS R, 
                ( ( Row_number() 
                      OVER( 
                        ORDER BY lastname) - 1 ) / 5 ) AS Q 
         FROM   (SELECT DISTINCT lastname 
                 FROM   adventureworks2012.person.person) X), 
     ctetagged 
     AS (SELECT lastname, 
                CASE 
                  WHEN r = 0 THEN 5 
                  ELSE r 
                END AS R, 
                q 
         FROM   cte) 
SELECT Max(CASE 
             WHEN r = 1 THEN lastname 
           END), 
       Max(CASE 
             WHEN r = 2 THEN lastname 
           END), 
       Max(CASE 
             WHEN r = 3 THEN lastname 
           END), 
       Max(CASE 
             WHEN r = 4 THEN lastname 
           END), 
       Max(CASE 
             WHEN r = 5 THEN lastname 
           END) 
FROM   ctetagged 
GROUP  BY q 
ORDER  BY q; 
/* 
Abbas  Abel  Abercrombie  Abolrous  Acevedo 
Achong  Ackerman  Adams  Adina  Agbonile 
Agcaoili  Aguilar  Ahlering  Ajenstat  Akers 
Alameda  Alan  Alberts  Albrecht  Albright 
Albury  Alcorn  Alderson  Alexander  Allen 
Allison  Alonso  Alpuerto  Altamirano  Altman 
.....*/ 

CASE Expression Blog: http://www.sqlusa.com/bestpractices/training/scripts/casefunction/

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012