Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2014
    Posts
    24

    Unanswered: PIVOT without Aggregate function?

    So I've found this:
    Pivoting Without Aggregation | T-SQL content from SQL Server Pro

    I tried to mimic this for what I need and keep getting an error.

    Anyone familiar with SQL PIVOT without aggregation?

    Code:
    SELECT *
    FROM     dbo.vTeamCoaches
    PIVOT (max(TeamId) FOR PersonId IN ([CoachFN], [CoachLN], [CoachEmail])) pvt
    Current result:
    Regina Burton ask@yahoo.com Null Null Null
    Robin Dillon rhd@gmail.com Null Null Null
    Kathy Janik kjs@gmail.com Null Null Null
    Jennifer Heurlin jhb@hotmail.com Null Null Null
    Heather Blasko heat@comcast.net Null Null Null

    The first Team has two different names in the same TeamID (not sure why it's not showing this column?), so when each TeamID has multiple person (AKA: Coach RE: MemberTypeID=2), I need to show them as a separate column which includes the FirstName, LastName, Email.

    End Result seeking this:
    TEA00001888 Regina Burton ask@yahoo.com Robin Dillon rh1@gmail.com
    TEA00001889 Kathy Janik kjs@gmail.com Null Null Null
    TEA00001890 Jennifer Heurlin jh@hotmail.com Null Null Null
    TEA00001891 Heather Blasko hea@comcast.netNull Null Null


    So should show the following columns:
    TeamID, FirstName, LastName, Email, (if more than one for TeamID the -->) CoachFN1, CoachLN1, CoachEmail1, CoachFN2, CoachLN2, CoachEmail2, etc ...
    Last edited by angelsupport; 08-18-14 at 13:27.

  2. #2
    Join Date
    Apr 2014
    Posts
    24
    Found info and got this far with a function in SQL

    Code:
    DECLARE @SQL nvarchar(max)
    
    SET @SQL = N'SELECT TeamID'
    
    SELECT @SQL = @SQL + ',MAX(CASE WHEN rownum='+CAST(a.rownum AS CHAR(5))+' THEN CoachFN ELSE '''' END) AS [CoachFN'+CAST(a.rownum AS CHAR(5))+']'
    FROM dbo.vTeamCoaches a GROUP BY a.rownum
    ORDER BY a.rownum
    
    SET @SQL = @SQL + N' 
    FROM vTeamCoaches
    GROUP BY Teamid
    ORDER BY Teamid'
    PRINT @SQL
    EXEC sp_executesql @SQL
    When I run it get the results I need EXCEPT
    1) I need to also have the CoachLN and CoachEmail to show as columns just like the CoachFN
    2) How do I do the above in a SQL View?

    I saved the Function but can't seem to call it out?

    See attached Function result, result comparison to SQL View having the data set, result, and result compare -02

    I could change the "CoachFN" to "PersonId" then link the view to the person table to pull the names? With this I definitely need to know how to use the above Function in a View.

    Either way is fine.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Please provide sample data - this will likely clarify your request hugely.
    What makes a record CoachFN1, etc?

    This definitely looks like a display issue, so consider pivoting in your front end application/presentation layer instead of in SQL.
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2014
    Posts
    24

    sample data

    See attached sample data

    and screen shots of results.

    I found out I needed to have the CREATE PROCEDURE first.

    So now I have the SP ("spTeamCoachPivot")
    And although I can EXEC spTeamCoachPivot in the SQL View and get the result, I am unable to save the View.

    I get an:
    Incorrect syntax near 'EXEC'

    I need to be able to save the View to use the results in another View.

    GOAL:
    to pull in the names of the coaches and link to the competition and events these coaches are part of.
    Attached Files Attached Files

  5. #5
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    So, it looks like the trick here is that you need to first unpivot the three fields before creating your ending pivot. I found this article in my research which put me on the right track.

    Code:
    WITH Base AS (
    SELECT RowNum, TeamID, PersonID, CoachFN AS [CoachFN], CoachLN AS [CoachLN], CoachEmail AS [CoachEmail]
    FROM #tbl
    )
    , norm AS (
    SELECT TeamID, ColName + CONVERT(varchar, RowNum) AS ColName, ColValue
    FROM Base
    UNPIVOT (ColValue FOR ColName IN ([CoachFN], [CoachLN],[CoachEmail])) AS pvt
    )
    SELECT *
    FROM norm
    PIVOT (MIN(ColValue) FOR ColName IN (
    [CoachFN1], [CoachLN1],[CoachEmail1], 
    [CoachFN2], [CoachLN2],[CoachEmail2], 
    [CoachFN3], [CoachLN3],[CoachEmail3], 
    [CoachFN4], [CoachLN4],[CoachEmail4], 
    [CoachFN5], [CoachLN5],[CoachEmail5]
    )) AS pvt
    Swap out #tbl with your table name. Note that you will have include however many iterations of coaches in the last pivot statement to ensure that you include them all.

  6. #6
    Join Date
    Apr 2014
    Posts
    24
    Thank you!

    I get the following SQL Execution Error:
    Executed SQL Statement ...

    Error Message: The type of column "CoachEmail" conflicts with the type of other columns specified in the UNPIVOT list.


    When I removed the CoachEmail from the UNPIVOT line it works but not with it

    ?

  7. #7
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    I am guessing that both CoachFN, and CoachLN are both varchar type? Is CoachEmail set up as a different type? You might need to use a convert statement to get it the same as the others for the unpivot to work.

  8. #8
    Join Date
    Apr 2014
    Posts
    24
    It's the same.
    when i try the CONVERT(varchar(50), CoachEmail)

    I get USE OF CONVERT FUNCTION might be unnecessary.

  9. #9
    Join Date
    Apr 2014
    Posts
    24
    both first and last names are varchar(50) and the email is varchar(100) in the table design

  10. #10
    Join Date
    Apr 2014
    Posts
    24
    I did a work around.
    i replaced the Coach to just PersonId
    since I can save the View now I then linked that view to get the Coach info per PersonId

    THANK YOU SO VERY MUCH!

  11. #11
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    Actually, you should probably do the converts on CoachFN and CoachLN to extend them to varchar(100). Apparently, unpivot is finicky about this and this seems to be the better option. When I revised my sample table to match your design the following seems to work.

    Code:
    WITH Base AS (
    SELECT RowNum, TeamID, PersonID, CONVERT(varchar(100),CoachFN) AS CoachFN, CONVERT(varchar(100),CoachLN) AS CoachLN, CoachEmail
    FROM #tbl
    )
    , norm AS (
    SELECT TeamID, ColName + CONVERT(varchar, RowNum) AS ColName, ColValue
    FROM Base
    UNPIVOT (ColValue FOR ColName IN ([CoachFN], [CoachLN],[CoachEmail])) AS pvt
    )
    SELECT *
    FROM norm
    PIVOT (MIN(ColValue) FOR ColName IN (
    [CoachFN1], [CoachLN1],[CoachEmail1], 
    [CoachFN2], [CoachLN2],[CoachEmail2], 
    [CoachFN3], [CoachLN3],[CoachEmail3], 
    [CoachFN4], [CoachLN4],[CoachEmail4], 
    [CoachFN5], [CoachLN5],[CoachEmail5]
    )) AS pvt

  12. #12
    Join Date
    Apr 2014
    Posts
    24
    ah gotcha!

    thank you very much for all your help!

    i would not have figured out using any of the articles I've come across or the one you've shared to make it work.

    not just yet. lots of learning to do

  13. #13
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    No problem. Actually, I learned a couple of things today as well.

Posting Permissions

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