locked
Dinamically set table columns SQL RRS feed

  • Question

  • In my database I have a View Revenues with fields ProductID, EditionID, Year, January, February, March,..., December.

    I need to make  query for each ProductID and EditionID all the year and months revenues in a single record, for a timespan of 3 years:

    In order to fullfill this task I make the query:

    select SELECT Y.*, Y1.[year], y1.jan, y1.Feb, y1.Mar, y1.Apr, y1.May, y1.Jun, y1.Aug, y1.Sept, y1.Oct, y1.Nov, y1.Dec,
    Y2.[year], y2.jan, y2.Feb, y2.Mar, y2.Apr, y2.May, y2.Jun, y2.Aug, y2.Sept, y2.Oct, y2.Nov, y2.Dec,
    Y3.[year], Y3.jan, Y3.Feb, Y3.Mar, Y3.Apr, Y3.May, Y3.Jun, Y3.Aug, Y3.Sept, Y3.Oct, Y3.Nov, Y3.Dec

    from (select distinct ProductID,EditionID from @revenue) Y left join (select * from revenues where [Year] = Year(getdate())) Y1 on Y.ProductID=Y1.ProductID and Y.EditionID=Y1.EditionID left join (select * from revenues where [Year] = Year(getdate())+1) Y2 on Y.ProductID=Y2.ProductID and Y.EditionID=Y2.EditionID left join (select * from revenues where [Year] = Year(getdate())+2) Y3 on Y.ProductID=Y3.ProductID and Y.EditionID=Y3.EditionID

    Instead of having just "Jan" in the filed month, I would like to have the column name for the months in a format like [month] + [year], for instance "Jan 2013".

    Monday, September 9, 2013 3:25 PM

Answers

  • Hi Trina,

    If you want dynamic column names, the only way is to use Dynamic SQL. If you think, it's worth it, I will modify the jan as "Jan+year" for an example, you could refer it to modify the other months, please refer to the codes below:

    declare @SQL varchar(max)
    set @SQL =
    
    'SELECT Y.*, Y1.[year],y1.jan as jan'+cast(year(getdate()) as char(5))+', y1.Feb, y1.Mar, y1.Apr, y1.May, y1.Jun, y1.Aug, y1.Sept, y1.Oct, y1.Nov, y1.Dec,
                       Y2.[year], y2.jan as jan'+cast(year(getdate())+1 as char(5))+', y2.Feb, y2.Mar, y2.Apr, y2.May, y2.Jun, y2.Aug, y2.Sept, y2.Oct, y2.Nov, y2.Dec,
                       Y3.[year], Y3.jan as jan'+cast(year(getdate())+2 as char(5))+', Y3.Feb, Y3.Mar, Y3.Apr, Y3.May, Y3.Jun, Y3.Aug, Y3.Sept, Y3.Oct, Y3.Nov, Y3.Dec 
    
    from (select distinct ProductID,EditionID from revenues) Y left join 
    	(select * from revenues where [Year] = Year(getdate())) Y1 on Y.ProductID=Y1.ProductID and Y.EditionID=Y1.EditionID left join 
    	(select * from revenues where [Year] = Year(getdate())+1) Y2 on Y.ProductID=Y2.ProductID and Y.EditionID=Y2.EditionID left join 
    	(select * from revenues where [Year] = Year(getdate())+2) Y3 on Y.ProductID=Y3.ProductID and Y.EditionID=Y3.EditionID '
    print @SQL
    execute (@sql)

    Thanks

    Candy Zhou

    • Edited by Candy_Zhou Tuesday, September 10, 2013 10:19 AM edit
    • Marked as answer by Trinakriae Monday, September 16, 2013 3:04 PM
    Tuesday, September 10, 2013 10:17 AM

All replies

  • Try to use PIVOT here, http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Monday, September 9, 2013 3:46 PM
  • Hi,

    Try this article - http://social.technet.microsoft.com/wiki/contents/articles/17510.t-sql-dynamic-pivot-on-multiple-columns.aspx


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Monday, September 9, 2013 4:54 PM
  • Thansk fro your reply! However with pivot I also take the data related to the months...there is not way to make something like

    y1.jan as 'jan'+ getdate(),..., y2.jan as 'jan'+ getdate() + 1,...

    Tuesday, September 10, 2013 8:47 AM
  • Hi Trina,

    If you want dynamic column names, the only way is to use Dynamic SQL. If you think, it's worth it, I will modify the jan as "Jan+year" for an example, you could refer it to modify the other months, please refer to the codes below:

    declare @SQL varchar(max)
    set @SQL =
    
    'SELECT Y.*, Y1.[year],y1.jan as jan'+cast(year(getdate()) as char(5))+', y1.Feb, y1.Mar, y1.Apr, y1.May, y1.Jun, y1.Aug, y1.Sept, y1.Oct, y1.Nov, y1.Dec,
                       Y2.[year], y2.jan as jan'+cast(year(getdate())+1 as char(5))+', y2.Feb, y2.Mar, y2.Apr, y2.May, y2.Jun, y2.Aug, y2.Sept, y2.Oct, y2.Nov, y2.Dec,
                       Y3.[year], Y3.jan as jan'+cast(year(getdate())+2 as char(5))+', Y3.Feb, Y3.Mar, Y3.Apr, Y3.May, Y3.Jun, Y3.Aug, Y3.Sept, Y3.Oct, Y3.Nov, Y3.Dec 
    
    from (select distinct ProductID,EditionID from revenues) Y left join 
    	(select * from revenues where [Year] = Year(getdate())) Y1 on Y.ProductID=Y1.ProductID and Y.EditionID=Y1.EditionID left join 
    	(select * from revenues where [Year] = Year(getdate())+1) Y2 on Y.ProductID=Y2.ProductID and Y.EditionID=Y2.EditionID left join 
    	(select * from revenues where [Year] = Year(getdate())+2) Y3 on Y.ProductID=Y3.ProductID and Y.EditionID=Y3.EditionID '
    print @SQL
    execute (@sql)

    Thanks

    Candy Zhou

    • Edited by Candy_Zhou Tuesday, September 10, 2013 10:19 AM edit
    • Marked as answer by Trinakriae Monday, September 16, 2013 3:04 PM
    Tuesday, September 10, 2013 10:17 AM
  • Refer the below dynamic pivot technique, (not tested)

    DECLARE
        @cols nvarchar(max),
        @stmt nvarchar(max)
    SELECT @cols = isnull(@cols + ', ', '') + '[' + T.[MonthYear] + ']' 
    FROM (SELECT DISTINCT cast([Year] as varchar(4)) + '-' + [Month] [MonthYear]
    		FROM REVENUES
    		WHERE [YEAR] BETWEEN Year(getdate()) AND Year(getdate()) + 2) as T
    SELECT @stmt = '
        SELECT * FROM (SELECT ProductID,EditionID,cast([Year] as varchar(4)) + '-' + [Month] [MonthYear]
    					FROM REVENUES WHERE [YEAR] BETWEEN Year(getdate()) AND Year(getdate()) + 2) T
            PIVOT 
            (
                COUNT(T.ProductID)
                for T.[MonthYear] in (' + @cols + ')
            ) as P'
    exec sp_executesql  @stmt = @stmt


    Regards, RSingh


    Tuesday, September 10, 2013 1:38 PM