Results 1 to 5 of 5

Thread: Pivotting Data

  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Pivotting Data

    I have this table:

    CREATE TABLE [dbo].[SHIP_HISTORY] (
    [SHIPID] [int] IDENTITY (1, 1) NOT NULL ,
    [PRODUCT] [nvarchar] (18) NOT NULL ,
    [ORDERNUM] [char] (10) NOT NULL ,
    [SHIP_DATE] [smalldatetime] NOT NULL ,
    [WHSE] [nvarchar] (5) NOT NULL ,
    [UNITS] [real] NOT NULL
    ) ON [PRIMARY]

    It contains 4 years of sales history.

    I need to pivot the data for a form in my front end (Access). The query was simple in Access, but too much data caused the form to be too slow.
    I'm thinking I'll pivot the data in SQL first, and just link my front end to the new table.

    I started to write a sproc to do this, and tested it before getting too far. For some reason, this is returning 166 rows, when I expect to seee only 4 (one for each year).
    There is one row for each year that contains my totaled data, and the rest of the rows contain zeros. Any ideas what would be causing this?

    Here's the SQL I'm using:

    SELECT YEAR(sHIP_DATE) AS YEAR,
    '1' = CASE WHEN DatePart(ww,[SHIP_DATE]) = 1 THEN SUM(UNITS)
    ELSE 0
    END,
    '2' = CASE WHEN DatePart(ww,[SHIP_DATE]) = 2 THEN SUM(UNITS)
    ELSE 0
    END
    FROM SHIP_HISTORY
    GROUP BY Year([SHIP_DATE]), DatePart(ww,[SHIP_DATE])

    If anyone has a better idea for how to do this, I'd welcome that, too!

    Thanks
    Inspiration Through Fermentation

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You are grouping by DatePart(ww,[SHIP_DATE]), and thus get rows for each of these values, even though you are not displaying DatePart(ww,[SHIP_DATE]) in your SELECT list.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try this:

    Code:
    SELECT	YEAR(sHIP_DATE) AS YEAR,
    	SUM(CASE WHEN DatePart(ww,[SHIP_DATE]) = 1 THEN UNITS ELSE 0 END) AS '1',
    	SUM(CASE WHEN DatePart(ww,[SHIP_DATE]) = 2 THEN UNITS ELSE 0 END) AS '2'
    FROM	SHIP_HISTORY
    GROUP BY Year([SHIP_DATE])
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    [edit] posted before reading your second post
    Last edited by RedNeckGeek; 02-16-05 at 11:56.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by blindman
    Try this:

    Code:
    SELECT	YEAR(sHIP_DATE) AS YEAR,
    	SUM(CASE WHEN DatePart(ww,[SHIP_DATE]) = 1 THEN UNITS ELSE 0 END) AS '1',
    	SUM(CASE WHEN DatePart(ww,[SHIP_DATE]) = 2 THEN UNITS ELSE 0 END) AS '2'
    FROM	SHIP_HISTORY
    GROUP BY Year([SHIP_DATE])

    That works perfectly.
    Thanks, Blindman!
    Inspiration Through Fermentation

Posting Permissions

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