Skip to Content
author's profile photo Former Member
Former Member

Dynamically Selecting columns in a query

Hello Everyone,

I have the following query,

/*Parameter Area*/

/*SELECT FROM [dbo].[OSRT] P0*/

declare @StartDate as datetime

/* WHERE */

set @StartDate = /* P0.FromDate */ '[%0]'

/*SELECT FROM [dbo].[OSRT] P1*/

declare @EndDate as datetime

/* WHERE */

set @EndDate = /* P1.ToDate */ '[%1]'

/* SELECT FROM [dbo].[OCRD] P2 */

declare @Vendor as varchar(30)

set @Vendor = /* P2.CardCode */ '[%2]'

/* SELECT FROM [dbo].[OSRN] P4 */

declare @Serial as varchar(30)

set @Serial = /* P4.DistNumber */ '[%4]'

Select Q1.CardCode, Q1.ItemCode, Q1.Dscription, SUM(Q1.LineTotal)

FROM (SELECT P1.CardCode, P1.DocDate, P2.ItemCode, P2.Dscription, P2.U_Unit, P2.U_UnitSer, P2.LineTotal FROM OPCH P1 INNER JOIN PCH1 P2 ON P1.DocEntry=P2.DocEntry) Q1

WHERE (Q1.ItemCode Like 'PROM%' AND Q1.CardCode=@Vendor AND Q1.DocDate BETWEEN @StartDate and @EndDate) OR (Q1.ItemCode Like 'PROM%' AND Q1.U_UnitSer=@Serial AND Q1.DocDate BETWEEN @StartDate and @EndDate)

GROUP BY Q1.CardCode, Q1.ItemCode, Q1.Dscription

Order By Q1.CardCode ASC

FOR BROWSE

Which successfully creates an expense like report for a user defined date range. Now, for reporting purposes, I'm being asked to have the query break down the totals by months rather than one lump sum. I've been thinking about trying to add a pivot to the query, but my problem falls into this: The user can select dates that flow across multiple years and I would still need to break it down by months without accidentally grouping them into the same. It's kind of stumping me as to how best to dynamically assign the columns based on a user input like that.

For additional points: I know they would like to see totals per column (IE: the totals of all the expense items shown for that month), but I'm not even worried about that one right now as I don't think I could really add that to the query in a way that would look 'clean' (IE: the total at the bottom of the column instead of all of them in a column to themselves.)

Thank you very much for your time,

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jan 07, 2014 at 04:25 PM

    On a bit of a happy note I've managed to confirm my query is generating almost exactly how I would wish it to (ie: my dynamic parts are creating the right column names and inserting the right values into my pivot query).

    So, I'm leaving that part aside. Here is a sample of the query with all the variables and dynamic parts taken out:

    Select Q1.CardCode, Q1.ItemCode, Q1.Dscription,

    'Aug 2013','Feb 2013','Jul 2013','Jan 2013','May 2013','Sep 2013','Dec 2013','Oct 2013','Mar 2013','Jun 2013','Apr 2013',

    Q1.LineTotal FROM (SELECT P1.CardCode, P1.DocDate, P2.ItemCode, P2.Dscription, P2.U_Unit, P2.U_UnitSer, P2.LineTotal FROM OPCH P1 INNER JOIN PCH1 P2 ON P1.DocEntry=P2.DocEntry) Q1 WHERE (Q1.ItemCode Like 'PROM%' AND Q1.CardCode='Mike Ronnie' AND Q1.DocDate BETWEEN DATEADD(year,-1,GETDATE()) and GETDATE()) OR (Q1.ItemCode Like 'PROM%' AND Q1.U_UnitSer='400' AND Q1.DocDate BETWEEN DATEADD(year,-1,GETDATE()) and GETDATE()) GROUP BY Q1.CardCode, Q1.ItemCode, Q1.Dscription

    Pivot

    (

    SUM(Q1.LineTotal)

    FOR

    (

    CONVERT(CHAR(4), Q1.DocDate, 100)

    + CONVERT(CHAR(4), Q1.DocDate, 120)

    ) IN

    ('Aug 2013','Feb 2013','Jul 2013','Jan 2013','May 2013','Sep 2013','Dec 2013','Oct 2013','Mar 2013','Jun 2013','Apr 2013')

    ) as P

    Order By Q1.CardCode ASC

    FOR BROWSE

    I keep getting the same error out of SQL management studio when I try to run the test, "

    Msg 156, Level 15, State 1, Line 4

    Incorrect syntax near the keyword 'Pivot'."

    Something is wrong with my pivot table, but I can't figure out what. Any help at all would be greatly appreciated.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 03, 2014 at 03:22 AM

    Hi,

    Control the year by using year function:

    year(P1.DocDate) = 2013. If user enter any date, the query will consider the year 2013.

    Thanks & Regards,

    Nagarajan

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 03, 2014 at 10:15 AM

    Dear Bradyn,

    Try to change your query like this :

    SELECT Q1.CardCode ,

    Q1.ItemCode ,

    Q1.Dscription ,

    YEAR(Q1.DocDate),

    month(Q1.DocDate),

    SUM(Q1.LineTotal)

    FROM ( SELECT P1.CardCode ,

    P1.DocDate ,

    P2.ItemCode ,

    P2.Dscription ,

    P2.U_Unit ,

    P2.U_UnitSer ,

    P2.LineTotal

    FROM OPCH P1

    INNER JOIN PCH1 P2 ON P1.DocEntry = P2.DocEntry

    ) Q1

    WHERE ( Q1.ItemCode LIKE 'PROM%'

    AND Q1.CardCode = @Vendor

    AND Q1.DocDate BETWEEN @StartDate AND @EndDate

    )

    OR ( Q1.ItemCode LIKE 'PROM%'

    AND Q1.U_UnitSer = @Serial

    AND Q1.DocDate BETWEEN @StartDate AND @EndDate

    )

    GROUP BY Q1.CardCode ,

    Q1.ItemCode ,

    Q1.Dscription,

    YEAR(q1.DocDate),

    month(q1.DocDate)

    ORDER BY Q1.CardCode ASC

    Is it what you want ?

    It will group by month & year, and show the total in each month.

    Thanks & Regards,

    Hendra

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 06, 2014 at 08:48 PM

    Hi Hendra,

    Sorry for a bit of a late reply. That query isn't exactly what I was looking for, but it does work. I might end up using it if what I was attempting doesn't work. I was actually hoping to have month+year as columns across the top in order to make it more report friendly.

    I came across a little dynamic example that I tried to modify to suit my purposes. The new query is as follows:

    /*Parameter Area*/

    /*SELECT FROM [dbo].[OSRT] P0*/

    declare @StartDate as datetime

    /* WHERE */

    set @StartDate = /* P0.FromDate */ '[%0]'

    /*SELECT FROM [dbo].[OSRT] P1*/

    declare @EndDate as datetime

    /* WHERE */

    set @EndDate = /* P1.ToDate */ '[%1]'

    /* SELECT FROM [dbo].[OCRD] P2 */

    declare @Vendor as varchar(30)

    set @Vendor = /* P2.CardCode */ '[%2]'

    /* SELECT FROM [dbo].[OSRN] P4 */

    declare @Serial as varchar(30)

    set @Serial = /* P4.DistNumber */ '[%4]'

    declare @months as int

    declare @i as int

    declare @cols as NVARCHAR(MAX)

    set @months=MONTH(@EndDate)-Month(@StartDate)

    SET @cols = STUFF((SELECT distinct ',' + (CONVERT(CHAR(4), c.DocDate, 100) + CONVERT(CHAR(4), c.DocDate, 120))

    FROM OPCH c

    WHERE c.DocDate BETWEEN @StartDate and @EndDate

    FOR XML PATH(''), TYPE

    ).value('.', 'NVARCHAR(MAX)')

    ,1,1,'')

    Select Q1.CardCode, Q1.ItemCode, Q1.Dscription, @cols

    FROM (SELECT P1.CardCode, P1.DocDate, P2.ItemCode, P2.Dscription, P2.U_Unit, P2.U_UnitSer, P2.LineTotal FROM OPCH P1 INNER JOIN PCH1 P2 ON P1.DocEntry=P2.DocEntry) Q1

    WHERE (Q1.ItemCode Like 'PROM%' AND Q1.CardCode=@Vendor AND Q1.DocDate BETWEEN @StartDate and @EndDate) OR (Q1.ItemCode Like 'PROM%' AND Q1.U_UnitSer=@Serial AND Q1.DocDate BETWEEN @StartDate and @EndDate)

    GROUP BY Q1.CardCode, Q1.ItemCode, Q1.Dscription

    Pivot

    (sum(Q1.LineTotal) FOR (CONVERT(CHAR(4), c.DocDate, 100) + CONVERT(CHAR(4), c.DocDate, 120)) IN (@cols)) P

    Order By Q1.CardCode ASC

    FOR BROWSE

    Now, this query just returns an error and I fear I might be expecting too much out of a pivot table. Any suggestions on cleaning up this query to make it work properly? Can it even work the way I'm imagining or am I expecting too much?

    Forcing the year to stay in 2013 is not an option unfortunately, this report has to work across multiple years just on the off chance.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.