Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 12th, 2007, 05:24 AM
Registered User
 
Join Date: Jul 2007
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Subquery problem - help anyone?

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


 
Old September 12th, 2007, 05:46 AM
Registered User
 
Join Date: Jul 2007
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

 
Old September 12th, 2007, 07:21 AM
Registered User
 
Join Date: Jun 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to lmorales Send a message via AIM to lmorales Send a message via MSN to lmorales Send a message via Yahoo to lmorales
Default

Hi,

Try with:

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

Regards,


Luis Morales

 
Old September 12th, 2007, 06:08 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Quote:
quote:Originally posted by lmorales
 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
 
Old September 26th, 2007, 04:07 AM
Authorized User
 
Join Date: Dec 2006
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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]







Similar Threads
Thread Thread Starter Forum Replies Last Post
Subquery debbiecoates SQL Server 2000 4 June 25th, 2008 03:49 AM
PHP/MySQL Subquery Problem gfackler MySQL 1 April 18th, 2007 11:31 PM
Do i have to use a subquery, if yes then how? code_lover SQL Language 2 January 2nd, 2007 02:22 PM
Help...subquery problem Twistdmojo Classic ASP Databases 4 September 14th, 2006 11:45 AM
SQL Select problem (subquery needed??) deian SQL Language 7 February 3rd, 2004 02:40 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.