0

Possible Duplicate:
Pivot using SQL Server 2000

I am using SQL Server 2000 and I need rows to be in columns...

My result set is like this.. resultset can have maximum of 11 rows or less then 11 rows..

batsman
-----------------
sachin
sahwag
dhoni
kohli
...
...
...

I want this:

batsman1      batsman2      batsman3    .....
--------------------------------------------------------
sachin        sahwag        dhoni       .....

Also, is there is a way to declare array in SQL Server 2000? If so, what is the syntax.

Community
  • 1
  • 1
Pramod
  • 33
  • 2
  • 8
  • 4
    This question covers the same ground (I think): http://stackoverflow.com/questions/312861/pivot-using-sql-server-2000 – rsbarro Apr 27 '11 at 03:53
  • Also, no arrays in SQL server. Look into using a table variables. It is the closest you will get in SQL Server. – JohnFx Apr 27 '11 at 03:58
  • 1
    This is easy to do but you need to show us some sample data or reformat your sample so it makes sense. Show us 5-10 rows in the resultset that should be converted to columns. Will there only be one row in the desired result or multiple rows? – Thomas Apr 27 '11 at 04:02
  • @rsbarro - in that question "A B C" are fixed but in my question the name of players varies from team to team.. so i am unable to understand how to use this type in my question.. – Pramod Apr 27 '11 at 04:16
  • i tried to format my example but i failed to show my data is row and columns... **batsmans** are in rows... and **batsman1**,**batsman2**,**batsman3**,**batsman4,.....** are columns.. – Pramod Apr 27 '11 at 04:19
  • @Pramod Check the link in the accepted answer (http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables) – rsbarro Apr 27 '11 at 04:22
  • @rsbarro - While that question might be a similar question, the accepted answer and the upvoted answer are less than desirable. – Thomas Apr 27 '11 at 05:25
  • This question is not an 'exact' duplicate. The other question assumes that you know exactly how many columns you want, whereas this one doesn't. The solution provided in that quetions would NOT work in this situation. – M.R. Apr 27 '11 at 19:51

2 Answers2

2

Typically the way you accomplish this is to use Case statements. First, this is what I think you are claiming you have which is a table with a column called Batsmans like so:

Batsmans
-------
Sachin
Sahwag
Dhoni
Kohli

What you seek is often called a crosstab query. Here is an example that will work in most database products:

Select Min( Case When Batsmans = 'Sachin' Then Batsmans End ) As Batsman1
    , Min( Case When Batsmans = 'Sahwag' Then Batsmans End ) As Batsman2
    , Min( Case When Batsmans = 'Dhoni' Then Batsmans End ) As Batsman3
    ...
From MyTable

This will produce a single row. Note that the columns are statically set as to whom you want first, second, third etc. This is often called a static crosstab for that reason. If you want the system to dynamically build the columns, you should build that sort of query outside of T-SQL in your middle-tier code.

If your table contained the position of the Batsmans, then you can use that to build your query

Position | Batsmans
---------  -------- 
1        | Sachin
2        | Sahwag
3        | Dhoni
4        | Kohli
Select Min( Case When Position = 1 Then Batsmans End ) As Batsman1
    , Min( Case When Position = 2 Then Batsmans End ) As Batsman2
    , Min( Case When Position = 3 Then Batsmans End ) As Batsman3
    ...
From MyTable
Thomas
  • 63,911
  • 12
  • 95
  • 141
0

Unfortunately, there is not 'pivot' in sql server 2000. So you have to pivot it manually. Knowing if you have the exact number of rows exactly should make this much easier, and you can follow a static crosstab approach - but you stated that you don't know if there will always have 11 rows (although, in a cricket team, you always should have 11 :). But try this - I tested this in a table called 'batsmen' as below. And although cursors are not recommended, knowing that you will have a max of 11 rows, it is acceptable.


ID          batsman         
----------- --------------------------------------------------
1           sachin
2           dhoni
3           sehwag




declare @id int
declare @batsman varchar(100)
declare @sqlStatment varchar(8000)
declare @counter int

declare getEm cursor local  for select ID, batsman from batsmen
set @sqlStatment = ''
set @counter = 1

open getEm
        while (1=1)
        begin
                 fetch next from getEm into @id, @batsman

                 if (@@fetch_status  0)
                    begin
                DEALLOCATE getEm
                break
                    end


              set @sqlStatment = @sqlStatment + '''' + @batsman + '''' + ' as batsman' + CAST(@counter as varchar(2)) + ','
              set @counter = @counter + 1   


        end


set @sqlStatment = 'select ' + LEFT(@sqlStatment,LEN(@sqlStatment)-1)

exec (@sqlStatment)

The output of this is:


batsman1 batsman2 batsman3
-------- -------- --------
sachin   dhoni    sehwag
M.R.
  • 4,737
  • 3
  • 37
  • 81