Dinamically set table columns SQL

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".

Answer 1

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.

Answer 2

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 **.

Answer 3

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,...

Answer 4

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

Answer 5

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