Date: 01/04/08 (SQL Server)    Keywords: database, web

Okay, now that the compatibility-level problem has gone the way of the great auk (until someone at the client decides to reset it again, I guess), I can now get to my underlying problem-- PIVOT, which I can't seem to understand from the overly-simplistic exampes I've found on the web.

Here's a small, heavily-edited version of the current crosstab query:

--create the pivot
INSERT INTO @m_tblItemDataPivot (brand, upc, item_desc, category, class, subclass, week_ending, week_1_dol, 
week_2_dol, week_3_dol, week_4_dol, week_5_dol, week_6_dol, week_7_dol, week_8_dol, ... 
SELECT DISTINCT i.brand, i.upc, u.item_desc, i.category, i.class, i.subclass, i.week_ending,
SUM(CASE p.period_sysno WHEN @m_intThisWeekSysno - 51 THEN phg.total_dollars ELSE 0 END) AS week_1_dol,
SUM(CASE p.period_sysno WHEN @m_intThisWeekSysno - 50 THEN phg.total_dollars ELSE 0 END) AS week_2_dol,
SUM(CASE p.period_sysno WHEN @m_intThisWeekSysno - 49 THEN phg.total_dollars ELSE 0 END) AS week_3_dol,
SUM(CASE p.period_sysno WHEN @m_intThisWeekSysno - 48 THEN phg.total_dollars ELSE 0 END) AS week_4_dol,
SUM(CASE p.period_sysno WHEN @m_intThisWeekSysno - 47 THEN phg.total_dollars ELSE 0 END) AS week_5_dol,
SUM(CASE p.period_sysno WHEN @m_intThisWeekSysno - 46 THEN phg.total_dollars ELSE 0 END) AS week_6_dol,
SUM(CASE p.period_sysno WHEN @m_intThisWeekSysno - 45 THEN phg.total_dollars ELSE 0 END) AS week_7_dol,
SUM(CASE p.period_sysno WHEN @m_intThisWeekSysno - 44 THEN phg.total_dollars ELSE 0 END) AS week_8_dol,...
FROM   ia_rep.dbo.pos_history phg (NOLOCK)			
INNER JOIN @m_tblSchickUPC u  ON phg.upc_sysno = u.upc_sysno
INNER JOIN @m_tblItemData i ON u.upc_code = i.upc
INNER JOIN ia_rep.dbo.periods p (NOLOCK) ON phg.period_sysno = p.period_sysno
    AND p.period_sysno BETWEEN @m_intThisWeekSysno-52 AND @m_intThisWeekSysno
	GROUP BY i.brand, i.upc, u.item_desc, i.category, i.class, 
            i.subclass, i.week_ending

Source: http://community.livejournal.com/sqlserver/65797.html