Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    40

    Unanswered: make cells a column

    My table

    Code:
    EMP_ID	[DATE]	Attendance
    ---------------------------
    106205	2011-02-01  A
    106206	2011-02-01  S
    106207	2011-02-01  A
    106205	2011-02-02  A
    106206	2011-02-02  A
    106207	2011-02-02  A
    I'm trying to write query wich would display it as following. So far I tried grouping, pivoting and still no luck.

    Code:
    	2011-02-01	2011-02-01
    106205	A		A
    106206	S		A
    106207	A		A

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    My first question for this issue is always "Why are you doing this in the database when formatting is the job of the interface?"

    My second question would be, "Can you post the code that you have tried?"

    Note: You must answer question #1 before I will address question #2....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2008
    Posts
    40
    Just incase someone is seeing this topic coz of the same issue I had here is the topic helped me to solve the problem.

    T-SQL Dynamic Pivot Table Examples for SQL Server 2008

    To be honest I thought it would be easier to do it in DB than in Aspx.VB page. I had done this in my aspx code behind. But I found doing it on SQL Server might be faster and less complicated.

    #2. I have managed to do it. So don't worry about it.

    I guess you know better than me. So lemme ask you the question. Is dynamic pivot table is better or long complicated VB code in Aspx is better? Which one would you prefer if you were in my case?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    It depends. If you care about only the original developers' convenience, then stick any logic in any layer. If you care about users and any maintenance programmers, then you put the display logic in the aspx layer. Why?

    1) If the performance is troublesome, additional web servers can be added a lot easier than additional database servers.
    2) The application is easier to understand, as each layer has predictable purposes.

    I am not sure I buy that the logic in VB would be all that difficult to create. Heck. It just seems to me it would be a few more if statements to detect if a header has changed, so long as the output of the query has been ordered properly.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You will notice that your dynamic pivot table produces different column headers each time you run it, whereas a normal view or stored procedure outputs a data set that has a reliable and consistent schema.
    Interfaces need this consistency in order to map local objects to database values. Your dynamic pivot table thus has little use to reporting tools and interfaces, which could very well have performed the pivoting themselves.
    The fact that interfaces have difficulty processing and parsing formatted data is one of the most important reasons why this formatting should not be done in the database.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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