0

I know that SQL Server 2000 doesn't support PIVOT table.

Let me tell you all about my issue and maybe you can help me with a solution.

I have a table with clients and projects.

client    project 
------    --------
A         1111
A         1112
B         2222
B         2223
C         1234
D         1235

I would like to swap rows with columns. So the output table will be:

A      B       C       D
---------------------------
1111   2222    1234    1235
1112   2223    NULL    NULL

It's possible in SQL Server 2008 simply using PIVOT table, but the server we need to use is 2000.

The clients and projects are dynamic of course.

Is there anything to get this result?

UPDATE:

I know that there are many questions in here about pivot in SQL Server. I read almost everything. One solution is to do many case statements, but this doesn't return what I need.

It will return: 

A       B       C       D
----------------------------
1111    NULL    NULL    NULL 
1112    NULL    NULL    NULL
NULL    2222    NULL    NULL
NULL    2223    NULL    NULL
NULL    NULL    1234    NULL
NULL    NULL    NULL    1235

Which is not what I want since I will be exporting it to EXCEL with drop-down lists and it will create a lot of empty strings in the lists.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mike
  • 1,302
  • 6
  • 23
  • 43
  • Had answered a similar question here [LINK to POST](http://stackoverflow.com/questions/22562167/sql-server-use-pivot-function-to-restruct-data/22562265#22562265) – SoulTrain Mar 24 '14 at 14:32
  • @Yuck, this will not work since it will create huge table where each row has only one project for one client. I'm trying to have one row with multiple project. – Mike Mar 24 '14 at 14:37
  • @SoulTrain, please see my additional comments – Mike Mar 24 '14 at 14:42
  • 4
    You are not asking for a pivot; you are asking for four independent lists in the four columns. This is a pain in SQL Server 2000. It is time to update. There are three major releases since then and the software is no longer supported (http://blogs.technet.com/b/dataplatforminsider/archive/2011/03/09/important-support-changes-for-sql-server-2000-and-sql-server-2005.aspx). – Gordon Linoff Mar 24 '14 at 14:49
  • Aside from the other recommendations / comments, in order to even simulate a pivot for what you want, you would need to have a sequence number per letter to compare first vs second, then it COULD be done easier. – DRapp Mar 24 '14 at 15:14
  • 1
    Fetch the data for one drop-down at a time, i.e. 4 queries like `SELECT Project FROM table WHERE Client = 'A'` – adrianm Mar 24 '14 at 15:53

0 Answers0