Generating a 2-D table of results in SQL

I have a list of items that is refreshed daily where each item falls into one of four price bands.

The following query will pull the counts of items in each price band by 开发者_如何转开发date, but it puts each band/date combination into its own row.

SELECT PB.band, Count(L.ID) as Count
FROM ListItem L
join PriceBand PB on L.price >= PB.loprice and L.price <= PB.hiprice
Where ListID = 1  and ranking <= 100 and band >= 1 and band <= 4
Group by date, PB.band
order by date asc, band asc

The result set looks like this (two days used for illustration):

date    band    Count
2010-12-08  1   20
2010-12-08  2   17
2010-12-08  3   32
2010-12-08  4   26
2010-12-09  1   19
2010-12-09  2   16
2010-12-09  3   33
2010-12-09  4   27

While I can iterate through this to create the table I really want, is there a reasonable way to write the query to return the data in a 2-D form as follows:

date        band1   band2   band2   band3   
2010-12-08  20      17      32      26
2010-12-09  19      16      33      27

Thanks all.

SELECT [date],
       COUNT(CASE WHEN PB.band = 1 THEN L.ID END) AS band1,
       COUNT(CASE WHEN PB.band = 2 THEN L.ID END) AS band2,
       COUNT(CASE WHEN PB.band = 3 THEN L.ID END) AS band3,
       COUNT(CASE WHEN PB.band = 4 THEN L.ID END) AS band4
FROM ListItem L
join PriceBand PB on L.price >= PB.loprice and L.price <= PB.hiprice
Where ListID = 1  and ranking <= 100 and band >= 1 and band <= 4
Group by date
order by date asc

Check out PIVOT clause in T-SQL. =>MSDN Page

I'm not sure about the exact form of the query, but it should look something like this (please let me know if it works):

SELECT [date],[1],[2],[3],[4]
FROM ListItem L
join PriceBand PB on L.price >= PB.loprice and L.price <= PB.hiprice
Where ListID = 1  and ranking <= 100 and band >= 1 and band <= 4
PIVOT(COUNT(L.ID) FOR PB.band IN([1],[2],[3],[4])) as pvt   
order by [date] asc

First, "date" is a horrible name for a column, so I hope that's just an example. Second, since you didn't use an alias with it, I have no idea which table it's actually in, so here's my best guess:

SELECT
    LI.list_date,
    SUM(CASE WHEN PB.band = 1 THEN 1 ELSE 0 END) AS band_1_count,
    SUM(CASE WHEN PB.band = 2 THEN 1 ELSE 0 END) AS band_2_count,
    SUM(CASE WHEN PB.band = 3 THEN 1 ELSE 0 END) AS band_3_count,
    SUM(CASE WHEN PB.band = 4 THEN 1 ELSE 0 END) AS band_4_count
FROM
    List_Item LI
INNER JOIN Price_Band PB ON
    PB.low_price <= LI.price AND
    PR.high_price >= LI.price AND
    PB.band <= 4
WHERE
    LI.list_id = 1 AND
    LI.ranking <= 100 AND
GROUP BY
    LI.list_date
ORDER BY
    LI.list_date