Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2012
    Posts
    3

    Question Unanswered: Do i need to pivot?

    Hi all,,

    this is my first post so please bare with me. If its something stupid please let me know and point to me to any links you think suitable.


    I currently have a query to do some inner joins on multiple tables.

    This is my sql Query im using

    Code:
    Select
     
                        agent_login_dbid,
                        P.last_name,
                        P.first_name,
                        P.user_name,
                        AL.login_code,
                        S.name,
                        SL.level_
    
                        From dbo.cfg_person P
     
                        INNER JOIN dbo.cfg_login_info LI ON P.dbid = LI.person_dbid
                        INNER JOIN dbo.cfg_agent_login AL ON LI.agent_login_dbid = AL.dbid
                        INNER JOIN dbo.cfg_skill_level SL ON P.dbid = SL.person_dbid
                        INNER JOIN dbo.cfg_skill S ON SL.skill_dbid = S.dbid"
    Unfortunately I will never know how many "name" 's that will be returned could be 1 upto 100

    However when my data is returned it returns it to multiple rows like the following

    Before - http://img832.imageshack.us/img832/2672/beforex.jpg


    and i would like to add to columns dynamically like the following.


    After - http://img823.imageshack.us/img823/1611/afterys.jpg

    If you can help, thank you
    Last edited by anthukne; 06-23-12 at 08:13. Reason: changed urls

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your links aren't useful, I can't resolve them.

    The short answer boils down to the old adage "Mange data with data managment tools, present data with Data presentation tools". Keep the result set consistant with a fixed number of consistantly named columns. Decide how to present the data in your presentation layer (which is a lot different on a web page than on a tablet or cell phone), and present the data appropriately based on the presentation device.

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

  3. #3
    Join Date
    Jun 2012
    Posts
    3
    Hi,

    I have attached the before and after images.

    Also just to let you know i need it as a query as this runs in C#

    Thanks
    Attached Thumbnails Attached Thumbnails before.jpg   after.jpg  

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The images you've posted confirm my previous statement.

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

  5. #5
    Join Date
    Jun 2012
    Posts
    3
    im calling this from C#

    Am i better off trying to manipulate it from there rather than the SQL Query?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think that you are definitely better off doing the pivot in your C# code than in your Transact-SQL code. The C# code can then have a consistant input result set (fixed number of columns and data types) and massage that data as needed to meet your display requirements.

    It isn't hard to pivot the data in Transact-SQL, but it is quite challenging to write a C# program to handle result sets that are inconsistant and it is harder still to adapt the C# later to present the variable result set data differently such as presenting a pie chart instead of a tablular form!

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

Posting Permissions

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