SQL Server + pivot table

Results 1 to 2 of 2

Thread: SQL Server + pivot table

  1. #1
    Join Date
    Dec 1969

    Default SQL Server + pivot table

    Hello<BR><BR>I have been using a database in Access that implements a pivot table, but I need to do the same thing in SQL Server and the SQL is a lot more complex. I&#039;m trying to do this with a stored procedure to take data in the format of a timestamp field and a text field with four different types in columns to produce rows with order by year/month with a count on the different field types. So;<BR>theDate_______type_check<BR>3/21/2003_______fail<BR>3/22/2003_______pass<BR>3/25/2003_______referred<BR>3/25/2003_______pass<BR>becomes<BR>Period___fail___pass ___referred<BR>3/2003____1_____2_______1<BR><BR>I am currently trying to do this with a t-sql case statement to do the count on each field type;<BR>Sum(Case when type_check = &#039;pass&#039; Then NumberofChecks Else 0 end) as typeCheck<BR>But I cannot get the select and group by clauses for the date part correct. Just wondered if anybody could help on this or had some generic code that I could build on.<BR><BR>Thanks

  2. #2
    Join Date
    Dec 1969

    Default RE: SQL Server + pivot table

    This is the best I can come up with at the moment...<BR><BR>select datepart(yy,theDate), datepart(m,theDate)<BR> ,sum(case type_check when &#039;pass&#039; then 1 else 0 end) as &#039;Pass&#039;<BR> ,sum(case type_check when &#039;fail&#039; then 1 else 0 end) as &#039;Fail&#039;<BR> ,sum(case type_check when &#039;referred&#039; then 1 else 0 end) as &#039;Referred&#039;<BR>from [TABLE]<BR>group by datepart(yy,theDate), datepart(m,theDate)<BR>order by datepart(yy,theDate), datepart(m,theDate)<BR><BR><BR>Gavin

Posting Permissions

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