Post reply

Need help on pivot query

  • December 1, 2014 at 11:23 am

    #397713

    This is my raw data.

    CREATE TABLE [dbo].[rawData](

    [paymentDte] [date] NULL,

    [zakatCode] [nvarchar](50) NULL,

    [zakatDescrp] [nvarchar](200) NULL,

    [zakatAmt] [decimal](10, 2) NULL

    ) ON [PRIMARY]

    GO

    INSERT [dbo].[rawData] ([paymentDte], [zakatCode], [zakatDescrp], [zakatAmt]) VALUES (CAST(0xF9310B00 AS Date), N'900', N'WOLVERINE', CAST(400.00 AS Decimal(10, 2)))

    INSERT [dbo].[rawData] ([paymentDte], [zakatCode], [zakatDescrp], [zakatAmt]) VALUES (CAST(0x9A330B00 AS Date), N'700', N'STORM', CAST(680.00 AS Decimal(10, 2)))

    INSERT [dbo].[rawData] ([paymentDte], [zakatCode], [zakatDescrp], [zakatAmt]) VALUES (CAST(0x88310B00 AS Date), N'900', N'WOLVERINE', CAST(2300.00 AS Decimal(10, 2)))

    INSERT [dbo].[rawData] ([paymentDte], [zakatCode], [zakatDescrp], [zakatAmt]) VALUES (CAST(0x99340B00 AS Date), N'340', N'BEAST', CAST(450.00 AS Decimal(10, 2)))

    INSERT [dbo].[rawData] ([paymentDte], [zakatCode], [zakatDescrp], [zakatAmt]) VALUES (CAST(0x55330B00 AS Date), N'700', N'STORM', CAST(300.00 AS Decimal(10, 2)))

    How is my query looks like to generate result as following,

    zakatDescrp | 2009 | 2010 | 2011

    --------------------------------------------------

    WOLVERINE2700.00 0 0

    STORM 0 710.00 0

    BEAST0 0 450.00

  • December 1, 2014 at 11:40 am

    #1761823

    Read the following articles to know how to pivot data using cross tabs and how to make it dynamic.

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

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    Here's an incomplete example:

    SELECT zakatDescrp,

    SUM(CASE WHEN YEAR(paymentDte) = 2009 THEN zakatAmt ELSE 0 END) [2009]

    FROM [dbo].[rawData]

    GROUP BY zakatDescrp;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

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

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