Friday, December 28, 2012 7:23 PM
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.
Jane Bill Chris Mike Joe
Friday, December 28, 2012 7:34 PM
Yeah you can do it using PIVOT query which will a better option.Any thing Else ? :)
To know :
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/
- Edited by Ahsan Kabir Friday, December 28, 2012 7:40 PM
- Proposed As Answer by Albeart Leaon Friday, December 28, 2012 8:26 PM
- Unproposed As Answer by Kalman TothMicrosoft Community Contributor, Moderator Saturday, January 05, 2013 11:32 PM
Friday, December 28, 2012 8:58 PM
MIN(CASE WHEN name = 'Bill' THEN somevalue END) AS Bill,
MIN(CASE WHEN name = 'Chris' THEN somevalue END) AS Chris,
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, firstname.lastname@example.org
Friday, December 28, 2012 9:37 PMModerator
;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 (,,,,)) 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
Saturday, January 05, 2013 11:30 PMModerator
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
- Edited by Kalman TothMicrosoft Community Contributor, Moderator Sunday, January 06, 2013 12:02 AM