Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    37

    Unanswered: Dynamic derivation of Heading - is it possible

    Hi
    I have a query which produces effectively a pivottable. Is there any way I can dynamically assign the column headings ie the code on each line after AS rather than hard coded as I have currently

    Extract of Current SP

    CREATE PROC dbo.FairValeSummaryPivot
    @BatchRunID INT
    AS
    SET NOCOUNT ON
    SELECT
    MIN(CASE WHEN Tn = '1' THEN PVBalance ELSE 0 END) AS 'Tn1 - Tn0' ,
    MIN(CASE WHEN Tn = '0' THEN PVBalance END) AS 'Tn0 - Tn-1' ,
    MIN(CASE WHEN Tn = '-1' THEN PVBalance END) AS 'Tn-1 - Tn-2',
    MIN(CASE WHEN Tn = '-2' THEN PVBalance END) AS 'Tn-2 - Tn-3',
    MIN(CASE WHEN Tn = '-3' THEN PVBalance END) AS 'Tn-3 - Tn-4',
    MIN(CASE WHEN Tn = '-4' THEN PVBalance END) AS 'Tn-4 - Tn-5',
    -- and so on
    FROM FVSummary
    WHERE BatchRunID = @BatchRunID
    GO

    what I would like would be along the lines of

    MIN(CASE WHEN Tn = '1' THEN PVBalance ELSE 0 END) AS 'Tn' + Tn + ' - Tn' + Tn-1, ,

    Hope this is clear
    Cheers

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Dynamic SQL is all that comes to my mind from the SQL perspective, but this is really a presentation issue so I think it should be handled at the client rather than in the SQL itself.

    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Dynamic SQL is the only method of assigning variable column headers. But I would discourage you from doing this because no reporting application (Crystal, Access, Active Reports...) is going to be able to deal with output that has a different layout for each result set.
    There is (almost) never a good reason for doing what you are trying to do, and in essence that is why it is difficult to do.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Do a google on ags crosstab. Also (if it's still out there) RAC for SQL.

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

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