Post reply

dynamic column generation

  • December 9, 2008 at 2:29 am

    #125556

    Hi,

    I have a query like given…Is there any way to give the RoomName dynamically. I have several roomnames in a table. So its not possible to write case for all of them like i have done here. Pls help ASAP. Thnx in advance.

    query

    ——–

    SELECT ItemCode,Sum(Estimate) as projecttotal,

    SUM(CASE WHEN RoomName =’Room1′ THEN Estimate ELSE 0 END ) AS [Room1],

    SUM(case when RoomName=’Room2′ then Estimate else 0 end) as [Room2]

    FROM ReportDataTable

    GROUP BY ItemCode

  • December 9, 2008 at 2:37 am

    #909508

    ani112001 (12/9/2008)

    Hi,

    I have a query like given…Is there any way to give the RoomName dynamically. I have several roomnames in a table. So its not possible to write case for all of them like i have done here. Pls help ASAP. Thnx in advance.

    query

    ——–

    SELECT ItemCode,Sum(Estimate) as projecttotal,

    SUM(CASE WHEN RoomName =’Room1' THEN Estimate ELSE 0 END ) AS [Room1],

    SUM(case when RoomName=’Room2' then Estimate else 0 end) as [Room2]

    FROM ReportDataTable

    GROUP BY ItemCode

    Yes, see Jeff Moden's excellent article here:

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply