Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2010
    Posts
    1

    Unanswered: CrossTab? Pivot? Stuck!

    I have a table with several records of information that I need to change up for an import into a new program that we are using. I have done something similar to this in the past, but I can't figure out what I am doing wrong. Any help is greatly appreciated.

    Here is my table structure...

    ID, contactphone

    Data looks like this...

    1234, 5551237890
    1234, 5551235678
    1234, 5551235632
    1222, 5551234567
    1222, 5551683325
    2333, 4573335676
    2333, 2344567646
    2456, 2346634674

    and so on...

    I need the data to come back with a table simliar to this.

    ID, phone1, phone2, phone3
    1234, 5551237890, 5551235678, 5551235632
    1222, 5551234567, 555,1683325, NULL
    2333, 4573335676, 2344567646, NULL
    2456, 2346634674, NULL, NULL

    Thanks again!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Nearly every client language has some kind of "Pivot Table" or "Crosstab" function/routine/whatever and this job is really best done on the client anyway. If you have one available within your tool or language of choice, use it!

    You can also do this in the database, but almost never as well as you can do it within the client.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2010
    Posts
    1
    So if we don't have tools to do this how can we do it in SQL as a view or just as a sql query?

    I would love to know how to make this work as well.

    Thanks,

    A

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What version and edition of Microsoft SQL are you using, what client language are you targeting, and what functionality do you need for the result? If both your SQL Server and your client language have XML support, then that woiuld be my first choice. If you don't have end-to-end XML support but you do have recursive CTE support, then that would be my choice. If you don't have any of them and don't need iterative support on the client then you can use a UDF or a stored procedure.

    This is really a job better handled by the client!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •