Represent multiple rows of data in one column as multiple columns in one row SQL server

Question

I have a table with the following structure and data:

batsman | runs | year

 1      | 800  | 2012
 1      | 950  | 2011
 1      | 1050 | 2010
 2      | 550  | 2012
 2      | 650  | 2011
 2      | 400  | 2010
 3      | 900  | 2012

This data needs to be Selected through a sql query as:

batsman | 2012 | 2011 | 2010

  1     | 800  | 950  | 1050
  2     | 550  | 650  | 400
  3     | 900  |  -   |  -

I'm trying to do this through a stored proc. The assumption can be made that the number of columns (in terms of years) is fixed: 3. Also note, there are no arithmetic operations necessary - all the numbers I need are already there, they just need to be represented column-wise.

You can use pivot function in SQL Server. You can find an example [here][1] [1]: stackoverflow.com/questions/15674373/… — Semih Yagcioglu
– Semih Yagcioglu, Commented Apr 30, 2013 at 11:29
Yes, I did hear the term with respect to this problem, but I'm not sure how to proceed with it... — Vinod Vishwanath
– Vinod Vishwanath, Commented Apr 30, 2013 at 11:29
@semihyagcioglu, that question looks helpful, thanks! I'll try it out. — Vinod Vishwanath
– Vinod Vishwanath, Commented Apr 30, 2013 at 11:30

Himanshu · Accepted Answer · 2013-04-30 11:37:39Z

3

There are several ways that you can convert the rows of data into columns.

In SQL Server you can use the PIVOT function:

select batsman, [2012], [2011], [2010]
from 
(
  select batsman, runs, year
  from yourtable
) d
pivot
(
  sum(runs)
  for year in ([2012], [2011], [2010])
) piv;

Or you can use an aggregate function with a CASE expression:

select batsman,
  sum(case when year = 2012 then runs else 0 end) [2012],
  sum(case when year = 2011 then runs else 0 end) [2011],
  sum(case when year = 2010 then runs else 0 end) [2010]
from yourtable
group by batsman;

The other version will work great if you have a known number of columns. But if you are going to have an unknown number of year values, then you will need to use dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(year) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT batsman,' + @cols + ' 
            from 
            (
                select batsman, runs, year
                from yourtable
            ) x
            pivot 
            (
                sum(runs)
                for year in (' + @cols + ')
            ) p '

execute(@query)

edited Apr 30, 2013 at 11:37

Himanshu

32.8k32 gold badges117 silver badges137 bronze badges

answered Apr 30, 2013 at 11:32

Taryn

249k57 gold badges375 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vinod Vishwanath Over a year ago

Thanks for the detailed answer. The actual table I'm dealing with is more complex than the one in my question, so I'll take a while to try out the solution. Thanks again!

TechDo · Accepted Answer · 2013-04-30 11:31:51Z

1

Please try PIVOT:

declare @tbl as table(batsman int, runs int, yearr int)
insert into @tbl values
(1, 800, 2012),
(1, 950, 2011),
(1, 1050, 2010),
(2, 550, 2012),
(2, 650, 2011),
(2, 400, 2010),
(3, 900, 2012)

select * From @tbl

select *
from
(
  select *
  from @tbl
) d
pivot
(
  max(runs)
  for yearr in ([2012], [2011], [2010])
) piv;

answered Apr 30, 2013 at 11:31

TechDo

18.7k3 gold badges55 silver badges64 bronze badges

Comments

KaraokeStu · Accepted Answer · 2013-04-30 11:31:55Z

1

You would need to use Pivot Tables as detailed here: http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx

For example:

select * from batsman
pivot (runs for Year in ([2012], [2011], [2010])) as runsperyear

answered Apr 30, 2013 at 11:31

KaraokeStu

7687 silver badges17 bronze badges

Collectives™ on Stack Overflow

Represent multiple rows of data in one column as multiple columns in one row SQL server

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related