|
Thanks for your reply, I will have a look at your code now.
My sites are a variable, but X,Y,Z are fixed (they are different types of accidents, and the data stored represents the number of occurances)
Just incase anybody want to have a go here is a create script
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Example](
[Site] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[X] [int] NOT NULL,
[Y] [int] NOT NULL,
[Z] [int] NOT NULL
) ON [PRIMARY]
insert into Example(Site, X,Y,Z) Values ('A',1,2,3)
insert into Example(Site, X,Y,Z) Values ('B',4,5,6)
insert into Example(Site, X,Y,Z) Values ('C',7,8,9)
select * from Example
|
|
|
|
|
This article [^]may be useful, I use MAX (min will do just as well) for the aggregator.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I think the main problem is I need multiple aggregates (for X, Y and Z).
I have this so far
select *
from Example
pivot
(
Max(X)
for site in ([A],[B],[C])
) as p
|
|
|
|
|
Not sure I understand your problem, are you saying you want 3 aggregates(X,Y,Z) for each type(columns) (A,B,C)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I think thats what I need, but not sure.
Basically I want to rotate the returned data by 90 degrees
|
|
|
|
|
You need to unpivot first, or normalize it actually, into something similar to this format:
CREATE TABLE [dbo].[Example](
[Site] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Accident] [nvarchar] NOT NULL,
[Occations] [int] NOT NULL,
) ON [PRIMARY]
Either in the query or a temp table, but preferably it should be stored normalized in the database
After that you can make the pivot the normal way
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Thanks, you were right 
I now have it working.
|
|
|
|
|
You should consider normalizing the table. It would make it a lot easier in the future to add functionality.
Consider this:
CREATE TABLE Incidents (
Site varchar
IncidentType varchar,
IncidentDate Date,
Incidentinfo varchar,
...
)Then just make your pivot on Select site,incidenttype,count(*) as incidentcount from incidents
You may also exchange site and incidenttype for IDs referencing tables holding info on sites and incidenttypes.
If you add a site to your organisation you simply add a row in a table.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
mark_w_ wrote: I think thats what I need, but not sure.
In that case work through the article because that is EXACTLY what it does with 2 columns, just extend it to meet your requirements.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
how to zip file with xp_cmdshell in sql server 2005
|
|
|
|
|
you can't personally I would use a cmd file that runs a winzip commandline application
Winzip[^]
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
Simon_Whale wrote: you can't
Why not?
If it runs an excutable, per your suggestion, then why would it not run what is already in windows?
|
|
|
|
|
tan873 wrote: how to zip file with xp_cmdshell in sql server 2005
I suppose that depends on exactly what you mean.
First step how would you do it in a command shell?
Windows has the 'compact' command built in.
Comand line options are documented in the help for that as well as at the following.
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/compact.mspx?mfr=true[^]
If you want to use something besides 'compact' then you must explore the command line options for that tool.
Once you know how to do it from a regular shell then you explore the options with the shell in sql server.
|
|
|
|
|
Hi!
I've to check the database for existency. i.e. My table has a field called name. I've to check whether a particular string is present in the field. e.g I've to check whether "ratha" is prsent in name. I'm using SQLite. How to do this? Which Query should I use?
|
|
|
|
|
Look at the LIKE keyword...
here...[^]
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
If I use LIKE key word,it doesn't fully serve my purpose. This is my query:
irr::core::stringc test = "Select name from profile WHERE name LIKE '%";
test += str.trim();
test += "%'";
result.clear();
result = pManager->SQLdb.Query(test);
With this query, I can't add a new name "sri" to the name field if "srinivasan" is already present in the name field. I don't want this. If "srinivasan" is present, I could not add names like "srinivasan","SRINIVASAN" or "SrInIvAsAn". i.e I've to check the exact name without regard to case. How to do this?
Another question is:
I've to restrict the number of records in a table. i.e I don't want to update the table if it already has 10 records in it. How to do this?
modified on Thursday, October 28, 2010 6:29 AM
|
|
|
|
|
T.RATHA KRISHNAN wrote: If I use LIKE key word,it doesn't fully serve my purpose.
It does however answer your first question.
With the additional detail that you have added then something like the following would be want you want. (Unless there are other yet unstated requirements.)
select lower(name) from profile WHERE name LIKE 'sri%"
T.RATHA KRISHNAN wrote: I've to restrict the number of records in a table. i.e I don't want to update the table if it already has 10 records in it. How to do this?
Odd requirement.
But do you know what 'count' does?
You might want to get a book or use the online documentation and read through all of the functions that are available as part of the database.
And if this system is part of a commercial/publicly available system you should look up sql injection attacks and ways to prevent that. Sql injection attacks are still a significant security problem.
|
|
|
|
|
hi
can we user parameters with the In function ??
example:
select * from tbl_Name where id in(par)
// par is a parameter pass to a stored procedure
i want to do this because i dont know how many ids i will get.
thank you
When you get mad...THINK twice that the only advice
Tamimi - Code
|
|
|
|
|
Example:
select * from tbl_Name where id in(1,2,3,4)
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
thank you..
i know this 
my question was how to do that with a parameter
When you get mad...THINK twice that the only advice
Tamimi - Code
|
|
|
|
|
Then you have to generate that numbers from select query.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
mmmmmm
suppose you have a check box list that contains 50 items(id)
and the user can select as many as he wish from the list..
then how you can pass the selected ids to a stored procedure ??
giving that you don't know how many ids you will pass??
got me??
When you get mad...THINK twice that the only advice
Tamimi - Code
|
|
|
|
|
Tamimi - Code wrote: then how you can pass the selected ids to a stored procedure ??
Presuming that you really did mean stored procedure then...
First step, determine based on to create the stored procedure in the language supported by the database. Options that I have used for variable argument lists.
- Arrays
- Varchar with values as a comma separated values in that list.
- Proc with up to X args and of which can be null.
- Several procs each one with an increasing number of parameters: first has 5, second has 10, etc.
- dynamic SQL, run in a proc (only suitable for situations where input is known to be secure.)
Once you do in fact have a proc then you write code which populates the parameters dependent on the type of proc that actually exists.
Conversely without a proc, and just using SQL, one creates the SQL from scratch using code (for loops, string concatenation, etc) with the appropriate number of bind variables. Then one populates the bind variables. Then you run it.
To my mind the last option is easier than any solution with procs.
|
|
|
|
|
I guess your parameter might be comprising of a delimited string, say, "1,2,4,8" or even "{Guid1},{Guid2},...". If such is your requirement, you can always use some little tweaks.
Like
Declare a temporary table to store the Ids, like declare @Ids table(Id int)
Then, use some string functions to split the string and insert the ids into the table. (Like, CHARINDEX, SUBSTRING, etc)
After that, you can then use the temporary table on your WHERE ... IN ... clause
Like
select * from tbl_Name where id in (select Id from @Ids)
|
|
|
|
|
thank you
this is nice. i will give it a try.
or simply 
string strIds = "1,2,3,4";
SqlCommand com="select * from tbl_name where id in(" + strIds + ")";
When you get mad...THINK twice that the only advice
Tamimi - Code
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
