0

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.

Improve this question
5
  • Am I right that you want a variable number of columns in your result? Commented Dec 29, 2009 at 9:55
  • @Mark; yes for the purpose to display values against each month and show month name in column Commented Dec 29, 2009 at 9:57
  • And you want the combined sum of Value1 for xyz from November from all years (you have data from both 2008 and 2009 in your example)? Commented Dec 29, 2009 at 9:59
  • @mark, I have edit my quesiton and correct the data, I have set year same, but Fianally I want is if year 2008 Nov and 2009 Nov spereate Commented Dec 29, 2009 at 10:02
  • Can you explain how you plan to call the query and how you will display the results? What technologies are you using apart from SQL? Commented Dec 29, 2009 at 10:44

2 Answers 2

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

No Doubt, you provide always greate solution, I love your solution. Thanks + 100000....... for you...... Again Thanks
Solution is working perfectly; but could you plz give me somes links that will help me to understand the query; I mean pivot and unpivot used in the query.
I am getting this error; "The type of column "Value2" conflicts with the type of other columns specified in the UNPIVOT list" because both column have different datatype SUM(Value1) Value1, sum(Value2) Value2; like first have int and 2nd one have decimal/float
I have Edit the value of value2 column; Thanks
Changed the answer to CAST Value1 to Float. Have a look.
|
0

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.

Improve this answer

3 Comments

Thanks for your reply, I need to show month name as column and populate data like wise....
I am using ASP.Net with SQL 2005
Then it seems fine to me to return the results in the form I showed above and use some simple ASP.NET code to tranpose the results into the columns you want. I'm not sure what the best way of doing this in ASP.NET is, but you might be able to use something like a DataTable to collate the results. You could iterate over the result set and every time you see a month/year combination that you haven't seen before, create a new column in your DataTable for that. Then bind your DataTable to a GridView when you are done. I'm fairly sure that this is easier than trying to write it all in SQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.