Results 1 to 6 of 6

Thread: Pivot Table

  1. #1
    Join Date
    Sep 2007
    Posts
    4

    Unanswered: Pivot Table

    I am trying to create a Pivot table on dates for a calender

    I get this error.

    Msg 512, Level 16, State 1, Line 5
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    *****************

    USE TrialGroup

    DECLARE @PivotColumnHeaders VARCHAR(MAX)
    SELECT @PivotColumnHeaders =
    ( select distinct [vcEvent]
    FROM Schedule
    )


    DECLARE @PivotTableSQL NVARCHAR(MAX)
    SET @PivotTableSQL = N'
    SELECT *
    FROM (
    SELECT userID,dtdate,vcEvent
    FROM dbo.Schedule ) DataTable
    PIVOT (
    Count (vcEvent)
    FOR vcEvent IN (
    ' + @PivotColumnHeaders + '
    )
    ) AS PivotTable'


    EXECUTE(@PivotTableSQL)

    ************************

    Here is what the data in the schedule table looks like

    idSchedule dtDate vcEvent userID
    2 2011-06-16 00:00:00 OOO jsmith
    3 2011-07-29 00:00:00 OOO jsmith
    4 2011-06-16 00:00:00 OOO jdoe
    9 2011-07-21 00:00:00 PTO jode


    Any idea's

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Code:
    SELECT @PivotColumnHeaders =
    ( select distinct [vcEvent]
    FROM Schedule
    )
    Multiple rows are returned, this is not supported. I guess what you want to do is something like:
    Code:
    DECLARE @PivotColumnHeaders VARCHAR(MAX)
    SET @PivotColumnHeaders = ''
    SELECT @PivotColumnHeaders = @PivotColumnHeaders + vcEvent + ', '
    FROM
    ( select distinct [vcEvent]
    FROM Schedule
    ) t
    SELECt @PivotColumnHeaders = substring(@PivotColumnHeaders,1,len(@PivotColumnHeaders)-1)
    I've not actually tested the code
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Sep 2007
    Posts
    4

    Question Close

    The code works

    returns

    ************

    userID dtdate OOO PTO
    jsmith 2011-06-16 00:00:00 1 0
    Jsmith 2011-06-16 00:00:00 1 0
    jdoe 2011-07-21 00:00:00 0 1
    jode 2011-07-29 00:00:00 1 0

    ****************
    But i'm looking for this
    Count of vcEvent Column Labels
    Row Labels 6/16/2011 0:00 7/21/2011 0:00 7/29/2011 0:00
    jsmith 1 1
    jdoe 1
    jobe 1
    Grand Total 2 1 1

    ************************

    What I'm confused at is why does it return multiple rows?, the top section returns just three dates (cuase of the distinct).

    Is that not the purpose of a pivot table take multiple rows and roll them up?

  4. #4
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Three dates = three rows
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  5. #5
    Join Date
    Sep 2007
    Posts
    4
    It is not three dates it is four dates, If you look at my original post you will see it is three DISTINCT Dates.

    What I am trying to do is take in the example 4 records that have 3 unique dates and make a pivot table with Username as Rows and Dates as Columns exactly as I put in the example which was built with Excel not SQL.

    I am trying to do this dynamically, all the syntax seems accurate.

    So my point of that is what is confusing me, The DISTINCT statement extracts 3 dates if run separate so why am I getting the error.

  6. #6
    Join Date
    Sep 2007
    Posts
    4
    Found a solution

    I created a view that only has the two fields, for some reason it only works when using a SELECT * FROM XXXX . If I specify fields doesn't work.

    *******************
    Use TrialGroup

    DECLARE @columns VARCHAR(8000)
    SELECT @columns = COALESCE(@columns + ',[' + cast(dtdate as varchar) + ']',
    '[' + cast(dtdate as varchar)+ ']')
    FROM vDSM_Dates
    GROUP BY dtdate

    DECLARE @query VARCHAR(8000)
    SET @query = '
    SELECT *
    FROM vDSM_Dates
    PIVOT
    (
    Count(dtdate)
    FOR dtdate
    IN (' + @columns + ')
    )
    AS p'
    EXECUTE(@query)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •