PIVOT QUERY HELP NEEDED

  • Hey Guys,

    Sorry for misunderstanding the requirements, I didn't realize multiple rows needed to be summed, but SUM() is the answer.

    Why do you need an extra COLUMN to hold totals? Why not a totals ROW at the end? It's much quicker and easier.

    Bob

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Do you mean the totals at the last row ? I need them at the end of every person. As this query is supposed to produce a list of people, the amounts given and the totals for each person. There will be an empty column for signing off.

    I am going to export the details to an excel sheet.

  • I went back and looked at your post again. To sum various amount columns into a single results column, you can do one of a couple of things. Let's say hypothetically that you only want to sum on two columns: ColA and Colb.

    In the primary query you can say

    SUM(ColA) as TotalA, SUM(ColB) as TotalB, SUM(ColA+ColB) as TotalWhatever

    --

    Or, you can make the primary query into a CTE and then take the total

    SELECT *, ColA+ColB as TotalWhatever FROM cte

    --

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Using Tom's query :-

    I have tried adding the total's column here

    'SELECT U_Casual_N, SUBJOBID, ' + @DATELIST +

    ' Total FROM ParamQry ' +

    But it overwrites on the last date column. Where can I add this extra column. How do I pick the amounts for a given row only ??

    Phew !!! This sounds pretty tricky !

    Should I create another pivot for this ?

  • Its tricky but if you look at Bobs post above on calculated columns ...

    you want your dynamic SQL to dynamically create a the final column as

    ( [Date1] + [Date2] + ... [DateN] ) AS RowTotal

    You may need a second @DATELIST variable to create it in, before adding it to the final SELECT

    I don't have my SQL Server access at the moment, - but post what you come up with, whether it works or not ..

    This is getting interesting 😉

  • Am getting this error here : -

    WITH ParamQry AS ( SELECT TRANSDATE,COSTTOTAL, U_Casual_N,SUBJOBID FROM dbo.ENPRISE_JOBCOST_JOBLINESVIEW WHERE SUBJOBID = 1007001 AND TRANSDATE BETWEEN '2009-04-08' AND '2009-04-14' ) SELECT U_Casual_N, SUBJOBID, [Apr 8 2009], [Apr 9 2009] , [Apr 10 2009] , [Apr 11 2009] , [Apr 12 2009] , [Apr 13 2009] , [Apr 14 2009] SUM([Apr 8 2009]), SUM([Apr 9 2009]) , SUM([Apr 10 2009]) , SUM([Apr 11 2009]) , SUM([Apr 12 2009]) , SUM([Apr 13 2009]) , SUM([Apr 14 2009]) FROM ParamQry PIVOT ( sum(COSTTOTAL) FOR TRANSDATE IN ( [Apr 8 2009], [Apr 9 2009] , [Apr 10 2009] , [Apr 11 2009] , [Apr 12 2009] , [Apr 13 2009] , [Apr 14 2009] SUM([Apr 8 2009]), SUM([Apr 9 2009]) , SUM([Apr 10 2009]) , SUM([Apr 11 2009]) , SUM([Apr 12 2009]) , SUM([Apr 13 2009]) , SUM([Apr 14 2009]) ) ) AS PVT;

    Msg 422, Level 16, State 4, Line 1

    Common table expression defined but not used. I placed the summations on the columns.

    -- Parameters

    DECLARE @StartDate Datetime;

    DECLARE @EndDate Datetime;

    DECLARE @SubjID INT;

    -- Parameter values

    SET @StartDate = '04/08/2009';

    SET @EndDate = '04/14/2009';

    SET @SubjID = 1007001;

    DECLARE @SQL nvarchar(max);

    DECLARE @DATELIST nvarchar(4000);

    SET @DATELIST = ''

    SELECT @DATELIST = CASE @DATELIST

    WHEN '' THEN '[' + Convert(nvarchar(11),MyDates.TRANSDATE,109) + ']'

    ELSE @DATELIST + ', [' + Convert(nvarchar(11),MyDates.TRANSDATE,109) + '] '

    END

    FROM ( SELECT DISTINCT TRANSDATE,COSTTOTAL FROM dbo.ENPRISE_JOBCOST_JOBLINESVIEW

    WHERE SUBJOBID= @SubjID

    AND TRANSDATE BETWEEN @StartDate AND @EndDate ) AS MyDates

    ORDER BY MyDates.TRANSDATE;

    DECLARE @DATELIST2 nvarchar(4000);

    SET @DATELIST2 = ''

    SELECT @DATELIST2 = CASE @DATELIST2

    WHEN '' THEN 'SUM([' + Convert(nvarchar(11),MyDates.TRANSDATE,109) + '])'

    ELSE @DATELIST2 + ', SUM([' + Convert(nvarchar(11),MyDates.TRANSDATE,109) + ']) '

    END

    FROM ( SELECT DISTINCT TRANSDATE FROM dbo.ENPRISE_JOBCOST_JOBLINESVIEW

    WHERE SUBJOBID= @SubjID

    AND TRANSDATE BETWEEN @StartDate AND @EndDate ) AS MyDates

    ORDER BY MyDates.TRANSDATE;

    SET @SQL = 'WITH ParamQry AS ( ' +

    ' SELECT TRANSDATE,COSTTOTAL, U_Casual_N,SUBJOBID' +

    ' FROM dbo.ENPRISE_JOBCOST_JOBLINESVIEW ' +

    ' WHERE SUBJOBID = ' + convert(varchar(8),@SubjID) +

    ' AND TRANSDATE BETWEEN ''' + Convert(varchar(10),@StartDate,120) + ''' AND ''' + Convert(varchar(10),@EndDate,120) + ''' ' +

    ' ) ' +

    'SELECT U_Casual_N, SUBJOBID, ' + @DATELIST + @DATELIST2 +

    'FROM ParamQry ' +

    'PIVOT ( sum(COSTTOTAL) ' +

    'FOR TRANSDATE IN ( ' + @DATELIST + @DATELIST2 +') ' +

    ') AS PVT;'

    PRINT @SQL;

    EXEC (@SQL);

  • Hi - Try this, it should give a Rowtotal column at the end of each row

    -- Parameters

    DECLARE @StartDate Datetime;

    DECLARE @EndDate Datetime;

    DECLARE @SubjID INT;

    -- Parameter values

    SET @StartDate = '04/08/2009';

    SET @EndDate = '04/14/2009';

    SET @SubjID = 1007001;

    DECLARE @SQL nvarchar(max);

    DECLARE @DATELIST nvarchar(4000);

    SET @DATELIST = ''

    DECLARE @DATELIST2 nvarchar(4000);

    SET @DATELIST2 = ''

    SELECT @DATELIST = CASE @DATELIST

    WHEN '' THEN '[' + Convert(nvarchar(11),MyDates.TRANSDATE,109) + ']'

    ELSE @DATELIST + ', [' + Convert(nvarchar(11),MyDates.TRANSDATE,109) + '] '

    END,

    @DATELIST2 = CASE @DATELIST

    WHEN '' THEN ' IsNull([' + Convert(nvarchar(11),MyDates.TRANSDATE,109) + '],0)'

    ELSE @DATELIST2 + ' + IsNull([' + Convert(nvarchar(11),MyDates.TRANSDATE,109) + '],0)'

    END

    FROM ( SELECT DISTINCT TRANSDATE,COSTTOTAL FROM dbo.ENPRISE_JOBCOST_JOBLINESVIEW

    WHERE SUBJOBID= @SubjID

    AND TRANSDATE BETWEEN @StartDate AND @EndDate ) AS MyDates

    ORDER BY MyDates.TRANSDATE;

    SET @SQL = 'WITH ParamQry AS ( ' +

    ' SELECT TRANSDATE,COSTTOTAL, U_Casual_N,SUBJOBID' +

    ' FROM dbo.ENPRISE_JOBCOST_JOBLINESVIEW ' +

    ' WHERE SUBJOBID = ' + convert(varchar(8),@SubjID) +

    ' AND TRANSDATE BETWEEN ''' + Convert(varchar(10),@StartDate,120) + ''' AND ''' + Convert(varchar(10),@EndDate,120) + ''' ' +

    ' ) ' +

    'SELECT U_Casual_N, SUBJOBID, ' + @DATELIST + ', (' + @DATELIST2 + ') AS RowTotal ' +

    'FROM ParamQry ' +

    'PIVOT ( sum(COSTTOTAL) ' +

    'FOR TRANSDATE IN ( ' + @DATELIST +') ' +

    ') AS PVT;'

    PRINT @SQL;

    EXEC (@SQL);

    I've combined setting @DATELIST and @DATELIST2 in the same SELECT

    @DATELIST2 should be a clause [Date] + ... + [DateN] - I've eliminated nulls to zero using IsNull

    The Clause is only required in the SELECT list of the final SQL, not the PIVOT part - you are still pivoting on the same date list,

    I can't check it as I don;t have dbo.ENPRISE_JOBCOST_JOBLINESVIEW but

  • you want your dynamic SQL to dynamically create a the final column as

    ( [Date1] + [Date2] + ... [DateN] ) AS RowTotal

    Now how could we do this? Let's think about it. We are dynamically creating a sql string... but it's just a string in a variable.... so we could attach another variable to it..... maybe we could at the same time develop another variable with all the same column names with plus signs between them and a constant "AS RowTotal" at the end... and then maybe just before we execute the SQL we tie the two (or three or four or.. ) strings together....

    Yeah... just maybe.

    Come on, guys. You can do this. It's string manipulation, not rocket science. 😉 It's fun to write code to write code. Next thing you know you'll be writing something that could evolve into SkyNet. :w00t:

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Did I detect something flying through the air?

    You old robot killing machine 😉

  • Woohooo ! It worked, it worked ! I wasn't really far from the solution.

    I need to understand more about queries If am to move from the simple SELECT statements to t-sql dynamic queries. Where can I start ?

  • martin.edward (5/23/2009)


    I need to understand more about queries If am to move from the simple SELECT statements to t-sql dynamic queries. Where can I start ?

    Start here for a stong understanding of how to write crosstab/pivot code...

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

    ... then move on to making it dynamic...

    http://www.sqlservercentral.com/articles/cross+tab/65048/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Ok guys, am not a SQL maestro, but I need to know it.

    The parameters we were using were hardcoded. They need to be input by the user. I guess this is part of dynamic SQL. How can I place the prompt ?

  • martin.edward (5/24/2009)


    Ok guys, am not a SQL maestro, but I need to know it.

    The parameters we were using were hardcoded. They need to be input by the user. I guess this is part of dynamic SQL. How can I place the prompt ?

    Where will the user be doing the input from? I'm thinking that if you make a parameterized stored procedure (a procedure that takes a parameters as inputs as part of it's call), then you should really be all set for most everything whether the user is doing it from a GUI or through SSMS.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks Tom for your help,

    After running the query I realised there is something amiss.

    Going back to the earlier structure

    NAME ID 1/2 2/2 3/2 4/2 5/2 JOBID

    Mark 9 200 NULL 200 NULL NULL 1001

    Peter 3 300 NULL NULL 300 NULL 1001

    Steve 1 400 200 NULL NULL 200 1001

    With the query when there are entries for the same date for diffrent persons I get the error

    The column 'Apr 9 2009' was specified multiple times for 'PVT'.

    and the rest of the error

    WITH ParamQry AS ( SELECT TRANSDATE,COSTTOTAL, U_Casual_N,SUBJOBID FROM dbo.ENPRISE_JOBCOST_JOBLINESVIEW WHERE SUBJOBID = 1007001 AND TRANSDATE BETWEEN '2009-04-08' AND '2009-04-14' ) SELECT U_Casual_N, SUBJOBID, [Apr 8 2009], [Apr 9 2009] , [Apr 9 2009] , [Apr 9 2009] , [Apr 10 2009] , [Apr 11 2009] , [Apr 12 2009] , [Apr 13 2009] , [Apr 14 2009] FROM ParamQry PIVOT ( sum(COSTTOTAL) FOR TRANSDATE IN ( [Apr 8 2009], [Apr 9 2009] , [Apr 9 2009] , [Apr 9 2009] , [Apr 10 2009] , [Apr 11 2009] , [Apr 12 2009] , [Apr 13 2009] , [Apr 14 2009] ) ) AS PVT;

    I have tried making this change - adding the U_Casual_ID in the datelist query but the error persists

    FROM ( SELECT DISTINCT TRANSDATE,COSTTOTAL,U_Casual_ID FROM dbo.ENPRISE_JOBCOST_JOBLINESVIEW

    WHERE SUBJOBID= @SubjID

    AND TRANSDATE BETWEEN @StartDate AND @EndDate ) AS MyDates

    ORDER BY MyDates.TRANSDATE,U_Casual_ID;

  • It's ok guys. I sorted it out. I had added the cost total as part of the distinct query. This brought about the error.

    This is what it should look like.

    FROM ( SELECT DISTINCT TRANSDATE FROM dbo.ENPRISE_JOBCOST_JOBLINESVIEW

    WHERE SUBJOBID= @SubjID

    AND TRANSDATE BETWEEN @StartDate AND @EndDate ) AS MyDates

    ORDER BY MyDates.TRANSDATE;

Viewing 15 posts - 16 through 30 (of 38 total)

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