How to convert rows into column in SQL Server 2000 [duplicate]

This question already has answers here: Closed 11 years ago.

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    .....
--------------------------------------开发者_StackOverflow中文版------------------
sachin        sahwag        dhoni       .....

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

---

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

---

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