Building SQL Query

Question

Hi all, I need some help to build SQL Query. I have table having data like:

ID   Date         Value1 Value2 Code
1   12/01/2009     4       3.5     abc
2   12/02/2009     3       4.0     abc
3   11/03/2009     6       8.5     xyz
4   11/01/2009     2       5.5     abc
5   11/02/2009     4       6.0     xyz
6   12/03/2009     5       7.0     xyz

I need to show result something like...

        ---------
Code   | Data   |    November(Sum of Values in month)      December   Jan   Feb
abc    | Value1 |           2                                 7       0       0
       | Value2 |           5                                 7       0       0
xyz    | Value1 |           10                                5       0       0
       | Value2 |           14                                7       0       0
       ----------

I need sum of value in each month as in above data in columns group by code

Thanks

Answer 1

This isn't quite what you asked for because the number of columns is fixed, but I think it's a better way to what you want in SQL:

SELECT Code, 'Value1' As Data, MONTH(Date) AS Month, YEAR(Date) AS Year, SUM(Value1) AS Sum
FROM Table1
GROUP BY Code, MONTH(Date), YEAR(Date)
UNION ALL
SELECT Code, 'Value2' As Data, MONTH(Date) AS Month, YEAR(Date) AS Year, SUM(Value2) AS Sum
FROM Table1
GROUP BY Code, MONTH(Date), YEAR(Date)
ORDER BY Code, Data, Month, Year

Example output:

Code Data   Month   Year    Sum
abc  Value1 11  2009 2
abc  Value1 12  2009 7
abc  Value2 11  2009 5
abc  Value2 12  2009 7
xyz  Value1 11  2009 10
xyz  Value1 12  2009 5
xyz  Value2 11  2009 14
xyz  Value2 12  2009 7

I'd recommend that you use a bit of non-SQL code to reformat the result into exactly what you asked for before displaying it to the user rather than trying to return a variable number of columns in SQL.

Answer 2

Have a look at this solution, and let me know what you think.

You have to use both PIVOT and UNPIVOT in this instance to get the result you are looking for. Hope this helps.

DECLARE @Table TABLE(
        ID INT,
        Date DATETIME,
        Value1 INT,
        Value2 FLOAT,
        Code VARCHAR(10)
)

INSERT INTO @Table (ID,Date,Value1,Value2,Code) SELECT  1,'12/01/2009',4,3,'abc'
INSERT INTO @Table (ID,Date,Value1,Value2,Code) SELECT  2,'12/02/2009',3,4,'abc'
INSERT INTO @Table (ID,Date,Value1,Value2,Code) SELECT  3,'11/03/2009',6,8,'xyz'
INSERT INTO @Table (ID,Date,Value1,Value2,Code) SELECT  4,'11/01/2009',2,5,'abc'
INSERT INTO @Table (ID,Date,Value1,Value2,Code) SELECT  5,'11/02/2009',4,6,'xyz'
INSERT INTO @Table (ID,Date,Value1,Value2,Code) SELECT  6,'12/03/2009',5,7,'xyz'


;WITH UnPvt AS (
        SELECT  *
        FROM    (
                    SELECT  Code,
                            DATENAME(MM, Date) MonthNameVal,
                            SUM(Value1) Value1,
                            SUM(Value2) Value2
                    FROM    (
                                SELECT  Code,
                                        Date,
                                        CAST(Value1 AS FLOAT) Value1,
                                        Value2
                                FROM    @Table
                            ) v
                    GROUP BY    Code,
                                DATENAME(MM, Date)
                ) Sub
        UNPIVOT
                (
                    Vals FOR RowValues IN (Value1, Value2)
                ) AS UnPvt
)
SELECT  *
FROM    UnPvt
PIVOT   (
            SUM(Vals)
            FOR MonthNameVal IN ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November], [December])
        ) AS pvt
ORDER BY Code, RowValues

Have a look at

• SQL SERVER – PIVOT and UNPIVOT Table Examples
• Give the New PIVOT and UNPIVOT Commands in SQL Server 2005 a Whirl
• Using PIVOT and UNPIVOT

Answer 3

This is short query and hope you will understand...

WITH UnPvt AS ( 
    SELECT  * FROM    ( 
                SELECT  Code, 
                        DATENAME(MM, ForecastDate) MonthNameVal 
                        ,SUM(BOBLets) BOBLets/*int*/, SUM(BOBRevenue) BOBRevenue/*Decimal(18,2)*/,
                        Cast((sum(BOBRevenue)/sum(BOBLets)) as Decimal(18,2)) adr /*Need to calculate here*/
                FROM    ( 
                            SELECT  Code, ForecastDate 
                                    ,CAST(BOBLets AS Decimal(18,2)) BOBLets,BOBRevenue 
                            FROM    VW_DailyForecast
                        ) v 
                GROUP BY  Code, DATENAME(MM, ForecastDate) 
            ) Sub 
    UNPIVOT 
            ( 
                Vals FOR RowValues IN (BOBLets, BOBRevenue,adr) 
            ) AS UnPvt 
)
SELECT  * 
FROM    UnPvt 
PIVOT   ( 
            SUM(Vals) 
            FOR MonthNameVal IN ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November], [December]) 
        ) AS pvt 
ORDER BY Code, RowValues desc

Error: Error:The type of column "adr" conflicts with the type of other columns specified in the UNPIVOT list.

Simple Table Schema:

ForecastDate ---Datetime
BOBLets  --- int
BobRevenue --- Decimal
Code --- Varchar(20)

OK, I found the problem. The line CAST(SUM()/SUM() AS DECIMAL(18,2)) is different to the other sums.

Cast all you top values to DECIMAL(18,2)

Something like this

;WITH UnPvt AS ( 
    SELECT  * FROM    ( 
                SELECT  Code, 
                        DATENAME(MM, ForecastDate) MonthNameVal ,
                        CAST(SUM(LastYearLets) AS DECIMAL(18,2)) LastYearRooms, 
                        CAST(SUM(LastYearRevenue) AS DECIMAL(18,2)) LastYearRevenue,
                        CAST(SUM(LastYearRevenue)/SUM(LastYearLets) AS DECIMAL(18,2)) LastYearADR,
                        CAST(SUM(BudgetLets) AS DECIMAL(18,2)) BudgetLets, 
                        CAST(SUM(BudgetRevenue) AS DECIMAL(18,2)) BudgetRevenue,
                        CAST(SUM(FCastLets) AS DECIMAL(18,2)) FcastLets, 
                        CAST(SUM(FCastRevenue) AS DECIMAL(18,2)) FCastRevenue,
                        CAST(SUM(BOBLets) AS DECIMAL(18,2)) BOBLets, 
                        CAST(SUM(BOBRevenue) AS DECIMAL(18,2)) BOBRevenue
                FROM    ( 
                            SELECT  Code, 
                                    ForecastDate ,
                                    CAST(LastYearLets AS Decimal(18,2)) LastYearLets,
                                    CAST(LastYearRevenue AS DECIMAL(18,2)) LastYearRevenue,
                                    CAST(BudgetLets AS Decimal(18,2)) BudgetLets,
                                    BudgetRevenue ,
                                    CAST(FcastLets AS Decimal(18,2)) FcastLets,
                                    FCastRevenue ,
                                    CAST(BOBLets AS Decimal(18,2)) BOBLets,
                                    BOBRevenue 
                            FROM    VW_DailyForecast 
                            where   forecastYear=2009 
                            AND     hotelID=1 
                            AND     SegmentID=1 
                        ) v 
                GROUP BY    Code, 
                            DATENAME(MM, ForecastDate) 
            ) Sub 
    UNPIVOT 
            ( 
                Vals FOR RowValues IN (LastYearRooms,LastYearRevenue,LastYearADR,BudgetLets,BudgetRevenue,FcastLets,FCastRevenue,BOBLets, BOBRevenue) 
            ) AS UnPvt 
)

SELECT  * 
FROM    UnPvt 
PIVOT   ( 
            SUM(Vals) 
            FOR MonthNameVal IN ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November], [December]) 
        ) AS pvt 
ORDER BY Code, RowValues desc

---

Final Query

WITH UnPvt AS ( 
    SELECT  * FROM    ( 
                SELECT  Code, DATENAME(MM, ForecastDate) MonthNameVal 
                        ,CAST(SUM(LastYearLets) as decimal(18,2)) LastYearRooms ,CAST(SUM(LastYearRevenue) as Decimal(18,2)) LastYearRevenue
                        ,CAST((SUM(LastYearRevenue)/SUM(LastYearLets)) as decimal(18,2)) LastYearADR
                        ,CAST(SUM(BudgetLets) as Decimal(18,2)) BudgetRooms, CAST(SUM(BudgetRevenue) as decimal(18,2)) BudgetRevenue
                        ,CAST((SUM(BudgetRevenue)/SUM(BudgetLets)) as decimal(18,2)) BudgetADR
                        ,CAST(SUM(FCastLets)as decimal(18,2)) FcastRooms, CAST(SUM(FCastRevenue)as decimal(18,2)) FCastRevenue
                        ,CAST((SUM(FCastRevenue)/SUM(FCastLets)) as decimal(18,2)) FCastADR
                        ,CAST(SUM(BOBLets)as decimal(18,2)) BOBRooms, CAST(SUM(BOBRevenue)as decimal(18,2)) BOBRevenue
                        ,CAST((SUM(BOBRevenue)/SUM(BOBLets)) as decimal(18,2)) BOBADR
                FROM    ( 
                            SELECT  Code, ForecastDate 
                                    ,LastYearLets,LastYearRevenue ,LastYearADR,BudgetLets,BudgetRevenue, BudgetADR
                                    ,FcastLets,FCastRevenue, FCastADR, BOBLets, BOBRevenue, BOBADR
                            FROM    VW_DailyForecast where forecastYear=2009 AND hotelID=1 AND SegmentID=1 
                        ) v 
                GROUP BY    Code, 
                            DATENAME(MM, ForecastDate) 
            ) Sub 
    UNPIVOT 
            ( 
                Vals FOR RowValues IN (LastYearRooms,LastYearRevenue,LastYearADR,BudgetRooms,BudgetRevenue,BudgetADR,FcastRooms,FCastRevenue,FCastADR,BOBRooms, BOBRevenue,BOBADR) 
            ) AS UnPvt 
)
SELECT  * 
FROM    UnPvt 
PIVOT   ( 
            SUM(Vals) 
            FOR MonthNameVal IN ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November], [December]) 
        ) AS pvt 
ORDER BY Code, RowValues desc