|
see the following example : it may help u (run it in sql query analyser)
CREATE TABLE #T1(id int,Name varchar(10))
insert into #T1 (id,Name) values (1,'Haris')
insert into #T1 (id,Name) values (2,'Arshad')
--select * from #T1
CREATE TABLE #T2(id int,Name varchar(5))
insert into #T2 (id,Name) values (1,'X')
insert into #T2 (id,Name) values (2,'Y')
insert into #T2 (id,Name) values (1,'Z')
insert into #T2 (id,Name) values (1,'X')
SELECT Id,Emp,X,Y,Z
FROM
(
SELECT #T1.Id,#T1.Name AS Emp,#T2.Name
FROM #T1
INNER JOIN #T2 ON #T2.Id = #T1.Id
) s
PIVOT
(
COUNT(Name)
FOR Name IN (X,Y,Z)
) p
drop table #T1
drop table #T2
fasih_is_my_signature
|
|
|
|
|
Thanks but i don't want to count. My rwq is little different and i would really appreciate if you could help me in this..
I have one table
CREATE TABLE #T1(id int,Name varchar(10))
insert into #T1 (id,Name) values (1,'Haris')
insert into #T1 (id,Name) values (2,'Arshad')
CREATE TABLE #T2(id int,Reason varchar(5))
insert into #T2 (id,Reason) values (1,'X')
insert into #T2 (id,Reason) values (2,'Y')
insert into #T2 (id,Reason) values (1,'Z')
insert into #T2 (id,Reason) values (1,'X')
Now i need output like
Id Name Reason1 Reason2 Reason3 ......ReasonN
1 Haris X Z X
2 Arshad Y NULL NULL
Thanks
Amit
|
|
|
|
|
I have never done a database search but am trying to search through a microsoft Access database with multiple tables. I am using visual studio 2005, and my project is a windows form based c++. Basically the user will enter what to search for and the program will go to the database and find all relevant entries. I just have no idea how to interact with the database. Any help will be appreciated. Thank you
|
|
|
|
|
You may want to research the LIKE keyword. If you have a table, lets call it Customers and it has a field called CustomerName, you could search for a customer who's name begins with the letter A by doing this SQL Select:
SELECT CustomerName FROM Customers WHERE CustomerName LIKE 'A%'
If you wanted all customers that have an A anywhere in their name:
SELECT CustomerName FROM Customers WHERE CustomerName LIKE '%A%'
Note that this is case sensitive, you would have to search for a and A if your data is not stored in all upper case...Like this:
SELECT CustomerName FROM Customers WHERE CustomerName LIKE '%A%' OR CustomerName LIKE '%a%'
Hope this helps.
|
|
|
|
|
I have simple situation...Here explicitly i am trying to insert char instead of integer and want to throw exception. But instead of printing "a" i am getting "Conversion failed when converting the varchar value 'a' to data type int." ....
ANY REASONS WHY???
CREATE TABLE Temp(C Int)
GO
INSERT INTO Temp VALUES('a')
IF @@ERROR <> 0
BEGIN
print 'a'
END
Amit
|
|
|
|
|
Use this way to to do so:
Declare @vError varchar(4000)
Select @vError = convert( varchar,@@Error)
IF @vError <> '0'
GOTO ErrorHandler
ErrorHandler:
IF @vError = '0'
BEGIN
Commit Tran
Return 0 -- On Success
END
Else
BEGIN
Rollback Tran
Return @vError -- On failure
END
fasih_is_my_signature
|
|
|
|
|
Hi all,
I want to add method that runs when i click DeleteButton on my GridView. Its button from <asp:CommandField ShowDeleteButton="true" />. I know how to solve it by simply adding new button on GridView for deleting and disabling the one from CommandField but thats not the way i want to solve it.
In GridView Load method row by row i choose DeleteButton and add OnClientClick method that shows ModalPopup and OnClick method (this method isnt firing when i click delete button).
<code>
protected void GridView1_Load(object sender, EventArgs e)
{
List<Control> myDeleteButtons = new List<Control>();
for (int i = 0; i < GridView1.Rows.Count; i++)
{
Tools.addAllControls(GridView1.Rows[i], myDeleteButtons);
((ImageButton)myDeleteButtons[4]).OnClientClick = "showPopup(this); return false;"; // working =-]
((ImageButton)myDeleteButtons[4]).Click += new ImageClickEventHandler(GridViewDelete); // not working =7
myDeleteButtons.Clear();
}
</code>
If any1 know how to solve it id be grateful to read that advice.
-------------------
n00b @ pr0grAmiNg
|
|
|
|
|
HI
there is no query analyser in my sys
how can i run a stored procedure or...
i want to change the owner of table to dbo using sp_changeobjectowner
Thank You
Hepsi
|
|
|
|
|
Please don't repeat post. I answer your query below, so you don't need to repost. You need to understand that people get to your posts in their own time, so you have to have a little patience.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
|
Hi there - hope someone can help with this...
Basically , I've created an app to migrate data from one database to another. Due to the large amounts of records involved, I decided to populate a dataset with the data from the original database and then update the destination by using the DataAdapter.
To show the user that something is going on, I have a progress bar. It's easy enough to increment the progress as each record is added to the dataset, but then there is a long wait as the dataset is being sent to the destination database.
Is there any event I can use to find out when each record has been added? Or can anyone else recommend a different approach?
EDIT: DOH! Have found the RowUpdated event - knew there'd be one somewhere!!!
Many thanks in advance
Baz
-- modified at 6:28 Tuesday 7th August, 2007
|
|
|
|
|
I don't know which database you are using, but this sounds like an extremely expensive operation. If you are using SQL Server, take a look at exporting data using DTS/SSIS. They are technologies that have been developed to cope exactly with this type of situation.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks for the reply, but got it sorted in the end
BTW, need to do it this way as the data is being processed before being chucked into the new database
Thanks anyway
Baz
|
|
|
|
|
DTS/SSIS performs what is known as ETL or Extraction, Transformation and Loading. This means that it is perfectly possible to process (transform) data as it is being exported.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
how i can check if my password is equal password of sa
i meaning i create view and i want check if password = password's sa
Create view V_Ar_Orders
As
if 'password' = Password's Sa
Select *
From Ar_Orders
Else
Print error
Go
i need do that
123
|
|
|
|
|
select * from syslogins where 1 = pwdcompare(@mypwd, password)
Regards
KP
|
|
|
|
|
Hi, I'm trying to simplify a stored procedure but have got stuck. I have a table that contains a list of 'items' and another that contains a list of 'options' associated with each 'item'.
I am currently executing the stored procedure for each item one by one. Obviously this is a bad way, as it hammers the server.
Is there a way to return the 30 options as 30 columns for each item/row?
Hope that makes sense..
Thanks in advance,
Phil Hodges
"Rules are for the obedience of fools and the guidance of wise men"
|
|
|
|
|
Phillip Hodges wrote: Is there a way to return the 30 options as 30 columns for each item/row?
Yes. But you haven't mentioned which database you are using so an example would be a bit of a stab in the dark hoping that you might be using the same database.
If you are using SQL Server 2005 you might want to look up the documentation for PIVOT
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
It's a really bad idea to try to "flatten" your structure this way. There are many ways that you could retrieve the data, but one approach would be to retrieve the data in the items information, and then perform a separate select inside the same procedure to retrieve the options based on the same criteria as you used to retrieve the item.
Then, all you need to do is (assuming you are using .NET), retrieve from the first table into a DataAdapter and retrieve the second table into the same DataAdapter as a new DataTable. In the data adapter, you would then create a relationship between the first DataTable and the second DataTable.
That's it. Search Google for creating master detail in ADO.NET.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Phillip Hodges wrote: Hope that makes sense..
Uh, well maybe, sort of, a little...
If the 30 options are fixed (constants), then maybe you do want a PIVOT table like Colin suggested. Otherwise, I am confused about what you want. Perhaps an example would help. Or, if you already have what you need, well that'll work too.
David
|
|
|
|
|
Hi i have imported tables from local to server and when i do those the tables will have owner as the project name or ..
But i'm unable to use the table in my stored procedure with a different owner its giving an error saying invalid object....
Can some one just help me by letting me know how i can change the owner to dbo...
If i create a table on the server then the owner is dbo only
but if i import only i'm getting this error
Hepsi
|
|
|
|
|
sp_changeobjectowner 'itemname', 'owner'
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
should i write a stored procedure can u explain it clearly PLEASE because i'm new to these...
THANK YOU
|
|
|
|
|
hepsy.i wrote: should i write a stored procedure
Errm no. Just execute it in Query Analyzer or Enterprise Manager.
Suppose you want to change the owner of the table myTable to dbo, you would enter
sp_changeobjectowner 'myTable', 'dbo' and then execute it directly (using F5).
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thank you very much ...
But my problem is i dont have query analyzer in my sys ..
Is there another way of doin this
Please help me..
THANK YOU
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
