SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



Display Data in Multiple Columns using SQL

SQL developers can display different data rows from a database table on multiple columns of a single line. You can imagine the output layout like multiple column page layout in MS Word, for example.

display data in 2 columns using SQL list table data in 3 columns using T-SQL SELECT data row to display in 4 columns using Row_Number() function

Displaying data in multiple columns is not a difficult task if you know how to approach the problem. This SQL tutorial will share a method using SQL Row_Number() function and SQL CASE conditional statements in order to fulfill this task.

If you run a SELECT statement on an entity table on any database, the result will be something like below. All data will be listed under a single column. But if the app developers want to display data not in a single column format, but more than one column.

display list as a single column select sql data to display as multiple columns

Assume that the web developer wants to display a movie list in 3 or 4-columns.
Then how should the database developers provide data to the user interface layer?
What should be the SQL code like that will be used to SELECT data in the desired format in SQL Server?

This Transact-SQL tutorial will be trying to answer all these questions and provide a solution method for SQL programmers for such requirements.

Let's start out SQL tutorial by defining our sample database table Movie which will store a list of movies. As a developer, our technical requirement is to provide most recent 100 movie titles as list formed of four columns of movie titles.
Actually this is like a database table which has 4 movie title columns.

For the sake of simplicity and less INSERT statements below, instead of 100 movie titles, I'll only list top 12 entities. So I only provide 12 INSERT INTO statements to populate database table movie with sample data.

create table movie (
 id int identity(1,1),
 title nvarchar(200),
 year int,
 genre varchar(40)
)

insert into movie select ('Star Wars'), 1977, 'Fantasy'
insert into movie select ('Spider Man'), 2002, 'Fantasy'
insert into movie select ('Stargate'), 1994, 'Sci-Fi'
insert into movie select ('Avatar'), 2009, 'Fantasy'
insert into movie select ('Timeline'), 2003, 'Sci-Fi'
insert into movie select ('Fantastic Four'), 2005, 'Fantasy'
insert into movie select ('The Phantom'), 1996, 'Action'
insert into movie select ('I, Frankenstein'), 2014, 'Fantasy'
insert into movie select ('The Lone Ranger'), 2013, 'Action'
insert into movie select ('Raiders of the Lost Ark'), 1982, 'Action'
insert into movie select ('RoboCop '), 2014, 'Action'
insert into movie select ('Need for Speed'), 2014, 'Action'

As I said before, if you have thousands of data and you are dealing only with a hundred of them, you can set the WHERE clause in below SQL SELECT as "RowNumber <= 100"

Such a change in WHERE clause will enable SQL developers to list only 100 entities (or in our case movie titles).

with cte as (
 select
  *,
  rownumber = ROW_NUMBER() over (order by year desc)
 from movie
)
select
 *,
 ((rownumber-1) / 4) + 1 as linenumber,
 ((rownumber-1) % 4)+1 as colnumber
from cte
where rownumber <= 12 -- this criteria can be updated for 100 rows

Since above SQL SELECT returns 12 rows on 4 columns, at the end of the process there will be only 3 lines each containing 4 movie titles.

display data in four columns on each row using Row_Number() function
SQL Row_Number() function used with Mod and Integer Division enables display data in columns and rows format

The next step after above raw data is created is to map titles in rows with line number 1 to first row of the next dataset, and rows with line number 2 to second row, etc.
While mapping entities to their new rows, we should be coding our SQL script so that the movie with colnumber 1 is under the first column, movie with colnumber 2 is under the second column, etc.

I'll now wrap the above SELECT statement with a new SQL CTE structure. I prefer to use a SQL CTE (Common Table Expression) instead of a sub-select statement because you can refer to CTEs more than once like a temp table. And using the title, row number and column number fields I'll create an additional SELECT statement in multiple-CTE form.

Here is the SQL codes which is one step improved on the above Select statement.

with cte as (
 select
  title,
  rownumber = ROW_NUMBER() over (order by year desc)
 from movie
), dataset as (
select
 title,
 ((rownumber-1) / 4) + 1 as linenumber,
 ((rownumber-1) % 4)+1 as colnumber
from cte
where rownumber <= 12
)
select
 linenumber,
 case colnumber when 1 then title else NULL end as column1,
 case colnumber when 2 then title else NULL end as column2,
 case colnumber when 3 then title else NULL end as column3,
 case colnumber when 4 then title else NULL end as column4
from dataset

As you can see the screenshot of the resultset of above SQL script, the mapping of title column of each table row is mapped to correct columns successfully.

display row data in multiple columns using SQL
SQL CASE statement on "column number" field will help SQL developers to display each row data as a column field

Now SQL GROUP BY statement for an aggregation on LineNumber column will aggregate 4 lines with sample linenumber into a single line resulting with the desired output for 4 column display.

with cte as (
 select
  title,
  rownumber = ROW_NUMBER() over (order by year desc)
 from movie
), dataset as (
 select
  title,
  ((rownumber-1) / 4) + 1 as linenumber,
  ((rownumber-1) % 4)+1 as colnumber
 from cte
 where rownumber <= 12
)
select
 linenumber,
 max(column1) as column1,
 max(column2) as column2,
 max(column3) as column3,
 max(column4) as column4
from (
 select
  linenumber,
  case colnumber when 1 then title else NULL end as column1,
  case colnumber when 2 then title else NULL end as column2,
  case colnumber when 3 then title else NULL end as column3,
  case colnumber when 4 then title else NULL end as column4
 from dataset
) list
group by linenumber

And finally our SQL SELECT statement produces the below desired output. SQL script listed given number of rows on different columns of a new result set. User interface programmers can now easily display below data on a gridview or data grid for a multi-column reporting.

display data rows in four columns using SQL in SQL Server
Here is the data rows listed in four columns using SQL programming







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems