Help with creating SQL Statement to get data from single table...

Hi, I'm having some difficulty creating the SQL Statement for getting some data from a table:

I have the following table of data

__User___Votes___Month

__A_______14______2
__A_______12______3
__A_______17______4
__A_______11______5

__B_______19______2
__B_______12______3
__B_______15______4

I want to beable to pull out the total number of votes a user has had over a period of months.

eg Total up each users users votes for months 4 and 5

that would give:

__User____TotalVotes

___A________28
___B________15

An added complecation is that user B does not have any data for month 5

Any help or pointers would be fanstatic

Many thanks

select sum(Votes)
from table
where month in (4, 5)
group by User|||Of cource! thank you.|||rather,
select
[user], sum(Votes) as TotalVotes
from
yourTable
where
month in (4, 5)
group
by [User]

Help with combining Sql Statements

I'm trying to combine the following two strings to create a single Insert statement (and thus only generate one record instead of two).

insertString ="Insert comments (uID) Select uID FROM users WHERE uName = @.uName"

insertString2 ="INSERT comments (eventID, text) VALUES ( @.eventID, @.comment)"

I have tried:

Insert comments (uID, eventID, text)SELECT uID FROM users WHERE uName = @.uNameVALUES(uID, @.eventID, @.comment)

Individually they work fine, but I can't get the syntax correct to allow them to work together. As you can tell, I'm not very good with SQL, so any help would be greatly appreciated!

Thanks in advance.

Try it like this:

INSERT INTO comments (uID, eventID, [text])SELECT uID,@.eventID, @.comment FROM users WHERE uName = @.uName

|||

Spot on, thank you very much!

Help with a simple Query

I am trying to make a single display page for an author's books.

the books page only displays books of a type "type" (novels, non-fiction, etc)

I would like to make it so that it can also show all books if "type" isn't selected. I THOUGHT the string would look like this:

<asp:SqlDataSource ID="SqlDSBooks" runat="server" ConnectionString="<%$ ConnectionStrings:csK2Reader%>" SelectCommand="SELECT * FROM [Books] ( If @.Type <> "" then WHERE ([Type] = @.Type)) ORDER BY [SortDate] DESC">

But it doesn't seem to want to work. I get a "server tag is not well formed" error.

Try this:

SELECT*FROM [Books]WHERE (@.TypeISNULLOR [Type]=@.Type)ORDERBY [SortDate]DESC

|||

Or, for text variables:

SELECT*FROM [Books]WHERE [Type] like IsNull(@.Type,'%')ORDERBY [SortDate]DESC

That is not, by the way, an exact equivalent of the previous poster's query.

This version allows a wildcard search, whereas the other query requires an exact match.

For example, if you were wanting to search for both Novels and Novellas, a value for @.Type of 'Novel%' would get you both types of book in one query.

Numbers and dates don't work this way. :(

Help with a Query

I am trying to get the following all into a single line, so i can run a
larger Query and have a nice simple table listing all the SQL servies
going on and when it was started.
-TIA-
CREATE TABLE #System_Monitor_Information_SQL_Informat
ion
SystemName varchar(50)
MSSQLServer varchar(50)
SQLServerAgent varchar(50)
SQLStartDate smalldatetime
ProductVersion varchar(50)
AuditDateTime smalldatetime
DECLARE @.datetime smalldatetime
SET @.datetime = (SELECT GETDATE())
INSERT System_Monitor_Information_SQL_Informati
on (SystemName,
AuditDateTime)
SELECT SystemName = @.@.SERVERNAME,
AuditDateTime = @.datetime
INSERT System_Monitor_Information_SQL_Informati
on (SQLStartDate)
SELECT crdate FROM master.dbo.sysdatabases
WHERE name = 'tempdb'
INSERT System_Monitor_Information_SQL_Informati
on (ProductVersion) EXEC
xp_msver 'ProductVersion'
INSERT System_Monitor_Information_SQL_Informati
on (MSSQLServer) EXEC
master..xp_servicecontrol 'QUERYSTATE', 'MSSQLServer'
INSERT System_Monitor_Information_SQL_Informati
on (SQLServerAgent) EXEC
master..xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent'
SELECT * FROM #System_Monitor_Information_SQL_Informat
ion
DROP TABLE #System_Monitor_Information_SQL_Informat
ionMatthew,
It's not clear how you want to treat multi-row results
from single-value results, but here's one snippet that
might help you. It gets the results of EXEC xp_msver
as a table, which should help. (You can get this bit
of information without xp_msver, too, using the
SERVERPROPERTY function, but I'm assuming
the question is how to get this kind of information
more conveniently.)
If you have a loopback linked server set up
UPDATE #System_Monitor_Information_SQL_Informat
ion SET
ProductVersion = (
SELECT Character_Value
FROM OPENQUERY(ME,'SET FMTONLY OFF; EXEC master..xp_msver
''ProductVersion''')
)
That isn't the most convenient solution, but you can also do this:
CREATE TABLE #ProductVersion (
s varchar(50)
)
INSERT INTO #ProductVersion
EXEC master..xp_msver 'ProductVersion'
UPDATE #System_Monitor_Information_SQL_Informat
ion SET
ProductVersion = (
SELECT s FROM #ProductVersion
)
DROP TABLE #ProductVersion
Steve Kass
Drew University
Matthew wrote:

>I am trying to get the following all into a single line, so i can run a
>larger Query and have a nice simple table listing all the SQL servies
>going on and when it was started.
>-TIA-
>CREATE TABLE #System_Monitor_Information_SQL_Informat
ion
> SystemName varchar(50)
> MSSQLServer varchar(50)
> SQLServerAgent varchar(50)
> SQLStartDate smalldatetime
> ProductVersion varchar(50)
> AuditDateTime smalldatetime
>DECLARE @.datetime smalldatetime
>SET @.datetime = (SELECT GETDATE())
>INSERT System_Monitor_Information_SQL_Informati
on (SystemName,
>AuditDateTime)
>SELECT SystemName = @.@.SERVERNAME,
> AuditDateTime = @.datetime
>INSERT System_Monitor_Information_SQL_Informati
on (SQLStartDate)
>SELECT crdate FROM master.dbo.sysdatabases
>WHERE name = 'tempdb'
>INSERT System_Monitor_Information_SQL_Informati
on (ProductVersion) EXEC
>xp_msver 'ProductVersion'
>INSERT System_Monitor_Information_SQL_Informati
on (MSSQLServer) EXEC
>master..xp_servicecontrol 'QUERYSTATE', 'MSSQLServer'
>INSERT System_Monitor_Information_SQL_Informati
on (SQLServerAgent) EXEC
>master..xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent'
>SELECT * FROM #System_Monitor_Information_SQL_Informat
ion
>DROP TABLE #System_Monitor_Information_SQL_Informat
ion
>
>|||To get the status of the service, though it is easier to use
xp_servicecontrol, it is not recommended due to its undocumented nature.
Otherwise, for the reminder of the script you can shorten the query as:
INSERT System_Monitor_Information_SQL_Informati
on
( SystemName, AuditDateTime, SQLStartDate, ProductVersion )
SELECT @.@.SERVERNAME, CURRENT_TIMESTAMP, crdate,
SERVERPROPERTY( 'ProductVersion' )
FROM master.dbo.sysdatabases
WHERE name = 'tempdb' ;
Anith

help with a query

Hi,
I have this select query:
select name from categories
it returns a lot of rows, but I want it to be in a single row separated by ,
so the result would be
"Games, Music, Gifts"
instead of
Games
Music
Gifts
how can I do that?
thanks,
BrunoIt looks like you're looking to do some pivottable stuff. This isn't
trivial to do using TSQL. You'd be better off doing this either on the
client or using MDX expressions.
-Alan|||Look at this example:
http://milambda.blogspot.com/2005/0...s-as-array.html
ML
http://milambda.blogspot.com/

Help with a CASE statement.

I am trying to get this case statement to work where it will
concatenate the values into a single string.
If I place anything with an equals sign such as (SELECT @.status_message
= @.status_message + 'variable') it give me an error. Any ideas. It
probably obvious but I am failing to see it.
DECLARE @.status_message varchar(100)
Set @.status_message = ''
SELECT * from master..sysdatabases
SELECT name, dbid, status, cmptlevel, filename,
CASE WHEN (status & 1073741824) <> 0 THEN 'cleanly shutdown'
WHEN (status & 4194304) <> 0 THEN 'autoshrink'
WHEN (status & 32768) <> 0 THEN 'emergency mode'
WHEN (status & 4096) <> 0 THEN 'single user'
WHEN (status & 2048) <> 0 THEN 'dbo use only'
WHEN (status & 1024) <> 0 THEN 'read only'
WHEN (status & 512) <> 0 THEN 'offline'
WHEN (status & 256) <> 0 THEN 'not recovered'
WHEN (status & 128) <> 0 THEN 'recovering'
WHEN (status & 64) <> 0 THEN 'pre recovery'
WHEN (status & 32) <> 0 THEN 'loading'
WHEN (status & 16) <> 0 THEN 'torn page detection'
WHEN (status & 8) <> 0 THEN 'trunc. log on chkpt'
WHEN (status & 4) <> 0 THEN 'select into/bulkcopy'
WHEN (status & 1) <> 0 THEN 'autoclose'
ELSE 'Unknown'
end
from master..sysdatabasesYou cannot assign values to variables in the same select statement in which
you also return a result-set to the client.
E.g. this is not allowed:
select <column list>
,@.<variable> = <some column>
from <table>
Put the variable assignment ina separate query, then include it in the one
that returns the result to the client.
select @.<variable> = <some column>
from <table>
select <column list>
,@.<variable> as <variable name>
from <table>
Does that answer your question?
ML
http://milambda.blogspot.com/|||Sort of, I know what the problems is, I am looking for a work around,
or a different solution that might achieve the same results. someone
said that i sould thorugh it into a loop, so i might try that.|||Anyone. I must be totaly brainfried.|||>> I am trying to get this case statement to work where it will concatenate
Which values are you talking about? The string values in the THEN clause of
the CASE? Please elaborate on what you are trying to do here.
What is the error message? Which piece of code are you trying to run to
generate the error?
Anith|||I figured it out
/ ****************************************
*******************
Returns a the STATUS of all databases on a server in English
****************************************
*******************/
SELECT @.@.SERVERNAME AS SERVER, VERSION, LEFT(name,30) AS [Databases],
DBID,
SUBSTRING(CASE status & 1 WHEN 0 THEN '' ELSE ',Aautoclose' END +
CASE status & 4 WHEN 0 THEN '' ELSE ',Select Into / Bulk Copy' END +
CASE status & 8 WHEN 0 THEN '' ELSE ',Truncate Log on Checkpoint' END +
CASE status & 16 WHEN 0 THEN '' ELSE ',Torn Page Detection' END +
CASE status & 32 WHEN 0 THEN '' ELSE ',Loading' END +
CASE status & 64 WHEN 0 THEN '' ELSE ',Pre-Recovery' END +
CASE status & 128 WHEN 0 THEN '' ELSE ',Recovering' END +
CASE status & 256 WHEN 0 THEN '' ELSE ',Not Recovered' END +
CASE status & 512 WHEN 0 THEN '' ELSE ',Offline' END +
CASE status & 1024 WHEN 0 THEN '' ELSE ',Read Only' END +
CASE status & 2048 WHEN 0 THEN '' ELSE ',dbo USE Only' END +
CASE status & 4096 WHEN 0 THEN '' ELSE ',Single User' END +
CASE status & 32768 WHEN 0 THEN '' ELSE ',Emergency Mode' END +
CASE status & 4194304 WHEN 0 THEN '' ELSE ',autoshrink' END +
CASE status & 1073741824 WHEN 0 THEN '' ELSE ',Cleanly Shutdown' END,
2,8000) AS OPTIONS_1,
SUBSTRING(CASE status2 & 16384 WHEN 0 THEN '' ELSE ',ANSI NULL default'
END +
CASE status2 & 65536 WHEN 0 THEN '' ELSE ',concat NULL yields NULL' END
+
CASE status2 & 131072 WHEN 0 THEN '' ELSE ',recursive triggers' END +
CASE status2 & 1048576 WHEN 0 THEN '' ELSE ',default TO local cursor'
END +
CASE status2 & 8388608 WHEN 0 THEN '' ELSE ',quoted identifier' END +
CASE status2 & 33554432 WHEN 0 THEN '' ELSE ',cursor CLOSE on commit'
END +
CASE status2 & 67108864 WHEN 0 THEN '' ELSE ',ANSI NULLs' END +
CASE status2 & 268435456 WHEN 0 THEN '' ELSE ',ANSI warnings' END +
CASE status2 & 536870912 WHEN 0 THEN '' ELSE ',full text enabled' END,
2,8000) AS OPTIONS_2, CMPTLEVEL, FILENAME
FROM master..sysdatabases|||On 27 Feb 2006 14:48:32 -0800, Matthew wrote:

>I am trying to get this case statement to work where it will
>concatenate the values into a single string.
>If I place anything with an equals sign such as (SELECT @.status_message
>= @.status_message + 'variable') it give me an error. Any ideas. It
>probably obvious but I am failing to see it.
>DECLARE @.status_message varchar(100)
>Set @.status_message = ''
>SELECT * from master..sysdatabases
>SELECT name, dbid, status, cmptlevel, filename,
>CASE WHEN (status & 1073741824) <> 0 THEN 'cleanly shutdown'
> WHEN (status & 4194304) <> 0 THEN 'autoshrink'
> WHEN (status & 32768) <> 0 THEN 'emergency mode'
> WHEN (status & 4096) <> 0 THEN 'single user'
> WHEN (status & 2048) <> 0 THEN 'dbo use only'
> WHEN (status & 1024) <> 0 THEN 'read only'
> WHEN (status & 512) <> 0 THEN 'offline'
> WHEN (status & 256) <> 0 THEN 'not recovered'
> WHEN (status & 128) <> 0 THEN 'recovering'
> WHEN (status & 64) <> 0 THEN 'pre recovery'
> WHEN (status & 32) <> 0 THEN 'loading'
> WHEN (status & 16) <> 0 THEN 'torn page detection'
> WHEN (status & 8) <> 0 THEN 'trunc. log on chkpt'
> WHEN (status & 4) <> 0 THEN 'select into/bulkcopy'
> WHEN (status & 1) <> 0 THEN 'autoclose'
> ELSE 'Unknown'
>end
>from master..sysdatabases
Hi Matthew,
Though concatenating these in a single string is presentation and
shouyld therefor be handled in the presentation layer, I'll give you a
working SQL solution:
SELECT name, dbid, status, cmptlevel, filename,
CASE WHEN (status & 1073741824) <> 0 THEN 'cleanly shutdown'
ELSE '' END
+ CASE WHEN (status & 4194304) <> 0 THEN 'autoshrink'
ELSE '' END
+ CASE WHEN (status & 32768) <> 0 THEN 'emergency mode'
ELSE '' END
(...)
+ CASE WHEN (status & 1) <> 0 THEN 'autoclose'
ELSE '' END
from master..sysdatabases
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:81e902hircfvvlacle0bg16ois08j7kmoq@.
4ax.com...
>.
> Though concatenating these in a single string is presentation and
> shouyld therefor be handled in the presentation layer,
>.
Hmm where have I heard this same idea before?
Once upon a time many were saying the same thing about
row numbering or worse line numbering.Surely this was
something to be done on the client whereas it had no
place being done on the server.(Not to mention the situation
was further muddled when the same voices used the very same
constructs as intermediate results in a query).Now along comes
Sql 2005 with some Sql-99 analytic functions like row_number()
and viola row numbering is no longer *presentation* and it's
perfectly fine to do on the server.To keep the analytics
company, 2005 introduces some more xml.Now we learn that
concatenating over rows can be done explicitly with it.
Presentation now has now become transformed into a serve-ice.
Since the underlying nature of 2005 hasn't changed
save for the mechanisms to do these things, it is now
*expedient* to do them on the server.Of course the shift
over even includes the dreaded crosstab with the ingenius
implementation of PIVOT.One must marvel at the ease of how
MS can change presentation to serve-ice:) But this shift may
come with some head scratching.Least some users wonder why
they can't simulate the serve-ice of 2005 to overcome the
presentation inherent in 2000.But one of the great things
about expediency is that it takes so shallow an explanation.
Flip flopping is alive and well in the world of sql.And for those
who actually study the subject academically I would say
't'where ignorance is bliss,t'is folly to be wise':)
$.03 from
www.rac4sql.net|||Well I guess as "proof" in SQL 2005 running the Execution Plan, the
cost associated for running query is exactly the same for both. So I
really boils down, which way is the "more correct" way to code.
e.g. which is better in practice.|||'which is better in practice' is just another way of saying what
is the most expedient way.Whatever works best for you:)
"Matthew" <MKruer@.gmail.com> wrote in message
news:1141230238.399541.6200@.z34g2000cwc.googlegroups.com...
> Well I guess as "proof" in SQL 2005 running the Execution Plan, the
> cost associated for running query is exactly the same for both. So I
> really boils down, which way is the "more correct" way to code.
> e.g. which is better in practice.
>

Help with a "select"

All of my customers are in a single data file, with one record per customer for each year that the customer bought something. (If customer A bought something in 1993, he would have a customer record in the file for that year. If he did not buy something in 2004 he would have no customer record for 2004 in the file.)

I want to "select" customer records from the file for which there is a 1993 record, but no 2004 record.

What would the select syntax be like?

Thanks in advance.Roughly:

Select * from Customers where Year(Fieldname) = '1993' and Customers.Cust_ID not in(select Cust_ID from Customers where Year(Fieldname) = '2004')

HELP URGENT!

Hi,
I can access my database. I get the message ......cant access...in single
user mode. How can I reset this?
ThanksThe first thing I would try is from EM, right-click on the
database>properties>Options tab, uncheck Restrict Access if it's checked.
"Chris" wrote:
> Hi,
> I can access my database. I get the message ......cant access...in single
> user mode. How can I reset this?
> Thanks|||Thanks!
"Jack" wrote:
> The first thing I would try is from EM, right-click on the
> database>properties>Options tab, uncheck Restrict Access if it's checked.
> "Chris" wrote:
> > Hi,
> > I can access my database. I get the message ......cant access...in single
> > user mode. How can I reset this?
> >
> > Thanks

Help Updating Tables From TAB File

Hi All

Im Really New To SQL Server and need to do the folowing - if anyone can help out i will be VERY happy :)

i have a single table in sql server containing products im selling

withing that table is 2 fields that i need to be able to update without changing any of the other fields in that line. (i need to be able to update price and stock field)

there is a field that is unique (field with the barcode)

the data i need to import is from a TAB text file which contains the barcode, stock and price field

hope this makes sense and if anyone can help thats great

cheers

BenHowdy

You can use DTS to import data which will suck data in from the tab file. However, unless you have a bit of experience with SQL its probably worth trying this on some dummy tables to get it right.
You could use DTS to import the tab data into a new table. Then write some code to work your way through the new table to update the existing production table.

If you wanted to update a column based on a unique column value in a table you could use:

Update <tabel_name>
set <column_to_be_updated> = '<some_value>'
where <unique_column> = '<some_value>'

i.e.

update table_product_data
set saleprice = '100'
where barcode = '097364543'

This ensures only the saleprice column in the table is changed where the barcode column = some unique value. That way you can selectively target a row in a particular column based on a value in the barcode column on the same row. Make sure that the barcode column ( or which ever column you use as the primary key for the table ) has unique values, otherwise you may get multiple rows in the saleprice column being updated at the same time.

Post back if probs.

Cheers,

SG.|||thanks for the reply

the problem is i am cool getting it to update 1 record its getting it to run thro a massive tab file and updating about 1500 prices

any suggestions for a thick person ?

cheers

ben|||right i have realised how to do this now - if anyone can give me some example code for in sql to loop thro i have made it import into a new table and i under stand the code you put before but im not sure how to make it loop thro

hope that makes sense!

cheers

ben|||Hi there!

Here I can give you a little example code. Let's say that:
1. "original" - is the table in the database
2. "new" - is the table which has been filled with the data out of the TAB file

update original
set original.stock = new.stock,
original.price = new.price
from original
,new
where original.bar_code_id = new.bar_code_id

Hope that helps you further. If not, post a reply!

Greetings,
Carsten

NOTE: This will not bring any new bar_code_id into "original"! It just updates existing id's|||thats great thanks

will that loop thro each result in the table new ?

thanks for the help

Ben|||you both rule!

its all working now:D:D - you cant believe how happy i am

please both check you private messages

thansk ben

Help required for Splitting up string variable using comma separator

I need a help in SQL Server 2000.

I am having a string variable in the format like -- (1,23,445,5,12)

I need to take single value at a time (like 1 for 1st, 23 for 2nd and so on) from the variable and update the database accordingly. This is like a FOR loop.

Can anyone help me out in splitting the variable using the comma separator...

You can just use the split command e.g.

Dim sAs String ="1,23,445,5,12"Dim splitAs String() = s.Split(",")For Each itemAs String In split Response.Write("Item: " & item &"<br>")Next

|||

I doesnt want this in VB.NET.

I want the same using SQL query in SQL Server 2000.

|||You'll have to create a function to do this. Here's a starting point:http://www.madprops.org/cs/blogs/mabster/archive/2005/12/05/T_2D00_SQL-to-Split-a-varchar-into-Words.aspx|||

Oh, sorry. If you want to do this in SQL, it's a bit harder as it doesn't really have built in string manipulation functions. If you were using a later version of SQL Server you could have registered that .NET code as a CLR function but as you ar using SQL Server 2000, you will have to do something like this:

CREATE PROCEDURE SplitString
@.yourStringvarchar(100)
AS
BEGIN
DECLARE @.StringCountint, @.mycountint, @.mystrlenint
DECLARE @.myvalvarchar(100)
set @.StringCount=Len(@.yourString)
set @.mycount=1
if (CHARINDEX(',',@.yourString,1)=0)
print @.yourString
WHILE (CHARINDEX(',',@.yourString,1)<>0)
BEGIN
if @.mycount=1
set @.myval=substring(@.yourString,@.mycount,CHARINDEX('^',@.yourString,1)-1)
print @.myval
set @.yourString =substring(@.yourString,Len(@.myval)+2,Len(@.yourString))
set @.StringCount= @.StringCount -1
if (CHARINDEX(',',@.yourString,1)=0)
print ,@.yourString
END
end
GO