Hi all,
Having one of those mornings where I can't get my head around anything and could use some fresh perspective on something.
I have a View which is laid out as follows:

DATE NAME QTY
10/9/07 Item A 12
10/9/07 Item B 7
10/9/07 Item C 192
11/9/07 Item A 13
11/9/07 Item B 2
11/9/07 Item C 1
12/9/07 Item A 162
12/9/07 Item B 13
12/9/07 Item C 27

What I need is an alternative view which looks like:
DATE Item A Item B Item C
10/9/7 12 7 192
11/9/7 13 2 1
12/9/7 162 13 27

My initial code read something like this:

SELECT DISTINCT Date,
       (SELECT Qty FROM View WHERE Name='Item A' as ItemA),
       (SELECT Qty FROM View WHERE Name='Item B' as ItemB),
       (SELECT Qty FROM View WHERE Name='Item C' as ItemC)
FROM View

and it works fine when there's only one date, but when I draw from a series of dates (as above), it returns an error saying "subquery returns more than one value". Also, when the list of items goes beyond A,B,C (I have maybe 15 categories to list), it's timing out, although this may be down to the inefficiency of how I was trying to do it.

Anyone offer any assistance?

Scott

Update: tinkered with it a little and this works -

SELECT DISTINCT Date,
       (SELECT Qty FROM View AS View1 WHERE ([Name] = 'Item A') AND(View.Date = Date)) AS ItemA,
       (SELECT Qty FROM View AS View1 WHERE ([Name] = 'Item B') AND(View.Date = Date)) AS ItemB,
       (SELECT Qty FROM View AS View1 WHERE ([Name] = 'Item C') AND(View.Date = Date)) AS ItemC
FROM View

However, as I add more Items (D,E,F etc.) to the list it slows and eventually just times out. Anything I can do to make it more efficient?

Hi,

Try with:

select DATE,NAME,QTY from your_view group by DATE,NAME,QTY order by DATE

Regards,


Luis Morales

Did you try that? All it does is show what's in the view... it doesn't put the Qty's for separate items in separate columns...

--Jeff Moden

Hi all,

You can use this very simple query -

Select [Date],
sum(case [Name] when 'Item A' then Qty else 0 end) as [Item A],
sum(case [Name] when 'Item B' then Qty else 0 end) as [Item B],
sum(case [Name] when 'Item C' then Qty else 0 end) as [Item C]
From Table_Name
Group by [Date]