Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2009
    Location
    India
    Posts
    14

    Question Unanswered: Creating column through T_SQL

    Is there any possibility to create columns through TRIGGER?
    I mean to say, if I have table name employee_register where column will be generated in the following way:
    Code:
    -----------------------------------------------------
    EMPCODE | 8/17/2009 | 8/18/2009 | 8/19/2009 | .....
    -----------------------------------------------------
    EMP001  |     1     |    0      |   <NULL>  | .....
    EMP002  |     0     |    0      |   <NULL>  | .....
    EMP003  |   <NULL>  |  <NULL>   |   <NULL>  | .....
    .
    .
    .
    -----------------------------------------------------

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    I don't recommend you try. Your output is a fairly standard cross-tab style report but it wouldn't be sensible to create a table that way. Write a query to generate that result and then put the query in a view or stored procedure.

  3. #3
    Join Date
    Feb 2009
    Location
    India
    Posts
    14
    Will you please explain me how to do this, am little bit confused!

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    I'll assume you have columns for Empcode and date. Try the following.

    Alternatively, any good reporting tool will have a cross-tab report feature built in without the need to do it yourself in a query. Many times it makes sense to do this in your reporting tool / presentation tier rather than in SQL. SQL is not a report writing language and has no display formatting capabilities at all.

    SELECT empcode,
    COUNT(CASE DATEDIFF(DAY,@StartDate,dt) WHEN 0 THEN 1 END),
    COUNT(CASE DATEDIFF(DAY,@StartDate,dt) WHEN 1 THEN 1 END),
    COUNT(CASE DATEDIFF(DAY,@StartDate,dt) WHEN 2 THEN 1 END),
    COUNT(CASE DATEDIFF(DAY,@StartDate,dt) WHEN 3 THEN 1 END)
    FROM tbl
    GROUP BY empcode;

  5. #5
    Join Date
    Feb 2009
    Location
    India
    Posts
    14
    Ok Sir, I'll search some good reporting tools.

Posting Permissions

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