|
Hi,
I have tables:
Fruits:
FruitID | FruitName
====================
1 | Apple
2 | Orange
3 | Pear
Baskets:
Basket | FruitID | Amount
========================
Bask1 | 1 | 3
Bask2 | 1 | 5
Bask3 | 1 | 2
Bask1 | 1 | 1
Bask2 | 1 | 2
Bask1 | 3 | 2
Bask3 | 3 | 4
What query can I use to get the following table:
Basket | Apple | Orange| Pear
=============================
Bask1 | 3 | 1 | 2
Bask2 | 5 | 2 | NULL
Bask3 | 2 | NULL | 4
Thanks in advance,
-----------------
Genaro
|
|
|
|
|
select fruits.*,baskets.*<br />
from fruits,baskets<br />
where fruits.FruitID = Baskets.FruitID
|
|
|
|
|
SELECT b.Basket AS Basket, b.Amount AS APPLE
FROM Fruits INNER JOIN
Baskets b ON Fruits.FruitID = b.FruitID
WHERE (Fruits.FruitID = 1)
SELECT c.Basket AS Basket, c.Amount AS ORANGE
FROM Fruits INNER JOIN
Baskets c ON Fruits.FruitID = c.FruitID
WHERE (Fruits.FruitID = 2)
SELECT d.Basket AS Basket, d.Amount AS PEAR
FROM Fruits INNER JOIN
Baskets d ON Fruits.FruitID = d.FruitID
WHERE (Fruits.FruitID = 3)
This will not give u the exact solution but will give you the partial solution.
Do good and have good.
|
|
|
|
|
This is the standard way of doing a Crosstab/PIVOT in SQL.
SELECT Basket,
SUM(CASE FruitID WHEN 1 THEN Amount ELSE 0 END) AS Apple,
SUM(CASE FruitID WHEN 2 THEN Amount ELSE 0 END) AS Orange,
SUM(CASE FruitID WHEN 3 THEN Amount ELSE 0 END) AS Pear
FROM Baskets
GROUP BY Basket
However, as you can see, you need to know in advance what the columns are. There is a new PIVOT command in SQL Server 2005 that may extend this functionality.
The other way is to de-normalise the data using
SELECT b.basket, f.FruitName, b.Amount
FROM Baskets b INNER JOIN Fruit f on b.FruitID = f.FruitID
and import it into Excel where you can use the Pivot tool to do the crosstabbing for you.
Ian
|
|
|
|
|
You could make a Stored Procedure that does this:
DECLARE @iColumns INT, @sql VARCHAR(2500),@cFruitName VARCHAR(10),@cFruitID varchar(10)
SET @sql = ''
DECLARE curFruits CURSOR FOR SELECT DISTINCT FruitID,FruitName FROM Fruits
OPEN curFruits
FETCH NEXT FROM curFruits INTO @cFruitID,@cFruitName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + ', SUM(CASE FruitID WHEN ' + @cFruitID + ' THEN Amount ELSE 0 END) as [' + @cFruitName + ']'
FETCH NEXT FROM curFruits INTO @cFruitID,@cFruitName
END
close curFruits
DEALLOCATE curFruits
SET @sql = 'SELECT Basket' + @sql + ' FROM Baskets GROUP BY Basket'
print @sql
exec(@sql)
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
First let me say that a few months ago I found the answer to this question and have forgottten it (That means I know the answer does exist)
Also, let me say that I do not want alternate solutions. I only want the exact solution I previously found.
There is a method to convert a variable of type varchar into some sort of structure directly usuable in an in clause, ie
SELECT * FROM customer WHERE customerID in *something*(@variable)
This method does not use instr, does not use loops, does not use custom split functions and looks very elegant in sql code. If anyone knows I would appreciate the answer. It is driving bonkers.
I am thinking it may have something to do with the TABLE function but the TABLE solutions I see on the web are not elegant and is not the one I am looking for.
On two occasions I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. - Charles Babbage
|
|
|
|
|
|
All of those solutions are fairly common, in fact I had already read that page. The one I want is very elegant. I know it exists because I have seen and used it but now I can't remember it!
On two occasions I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. - Charles Babbage
|
|
|
|
|
|
Yes I have, which is weird because I would have thought the ask tom authors would know.
What I am looking for actually exists but it is so hard to find (Trust me I am not jerking any chains). I am sure the method is a hack based on ETL functions but searching reveals nothing. I just wish I could remember the page where I originally read it. In fact, since I have had so much trouble finding it again, if I do refind it I will definately make it into an article. Even though CP isn't known for oracle buffs :p
On two occasions I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. - Charles Babbage
|
|
|
|
|
I have another question regarding datasets and datarows/columns.
I populated a combobox with this:
SqlCommand cmdSelect = sqlConnection1.CreateCommand();
cmdSelect.CommandText = "SELECT EmpID,EmpName FROM Employees";
SqlDataAdapter sdaEmp = new SqlDataAdapter();
sdaEmp.SelectCommand = cmdSelect;
sdaEmp.Fill(dstEmp,"Employees");
cboEmployee.DataSource = dstEmp.Tables["Employees"];
cboEmployee.DisplayMember = "EmpName";
cboEmployee.SelectedIndex = 0;
Now what I want to do when the user changes the 'selected index' of the combo box, to grab the info in the corresponding EmpId column from the datatable above.
What would be the correct porocedure to accomplish this?
Thanx in advance!
Jude
Jude
|
|
|
|
|
TheJudeDude wrote: Now what I want to do when the user changes the 'selected index' of the combo box, to grab the info in the corresponding EmpId column from the datatable above.
Use event Onselected index change
|
|
|
|
|
I am sorry I didn't make question more clear. I know the event to use, but I do not know how to access the data in the datatable that matches the same row as the selected index. IE:
EmpName EmpID (Selected Index)
John Doe 123 0
Jane Doe 2112 1
Bob Smith 9765 2
Lillte Johnny 321 3
If the user selects Bob Smith from the cobmobox, the selected index is 2. How to I retrieve the EmpId from that row?
Jude
|
|
|
|
|
Hello,
I know this is a very common connectivity error but I am really stuck here. I am currently trying to access a DB via a BindingNaviagator control via .NET. I know the connection string is 100% correct as when I do the preview data on my TableAdapter the data comes through. When I go to fire the program and access these records, I get the SqlException - Login failed for user (user) error. Like I said I know this is a pretty general error, but any advice on where to continue looking would be greatly appreciated.
Thanks In Advance
-Nate Lindley-
.NET Aficionado
|
|
|
|
|
That would suggest that the account the preview in Visual Studio is using is different to the account your application is using. This is possible if you are using a trusted connection as the account that will be used to access SQL Server will be the account of the currently running process.
I suggest you find out what account your application is using and set up the appropriate permissions on SQL Server, or change the account your application is running in.
|
|
|
|
|
I was thinking the same thing but I did the connection over and it still didnt work. I came to the conclusion that it was the DB settings not letting it connect because I actually created the DB on the server as opposed to the System Admin. So I had the SA make a new DB and it worked like a charm. I am not sure what settings were causing the remote connections to not be allowed but its all good now. Thanks for the response.
Big Pimpin, Spendin Gs
Nathan Lindley, .NET Aficionado
|
|
|
|
|
Can any body tell me how i can get the ip address of sql server 2000 in my lan to a text box in my vb.net application.
|
|
|
|
|
its someone how can tell me how can i make ODBC SQL Server and if is some link were can i learn?
Bravoone
|
|
|
|
|
Flaviu_2006 wrote: can i make ODBC SQL Server
What do you mean by that?
|
|
|
|
|
Sir,
CComPtr<Fields> pComFields;
CComPtr<Field> pComField;
m_SrcRecordSet->get_Fields(&pComFields);
pComFields->get_Item((CComVariant)FieldName,&pComField);
pComField->get_Value(&PVal);
DTEnum = pComField->Type;
What is wrong with the aove code ? I cannot retive the field value if the field type is string or number?
Why this is happen
Please help me?
Thanks
|
|
|
|
|
How do I export data from SQL to Excel using C#?
|
|
|
|
|
|
can nyone suggest how to right align data using sql query
nilesh
|
|
|
|
|
SQL Server Query Help (Trying to compare two tables and display the nonmatching records)otherwise delete data from one table after comparing it to another table 
|
|
|
|
|
A vague question deserves a vague answer:
SELECT
Table1_Id
FROM
Table1
WHERE
Table1_FK NOT IN (SELECT
Table2_Id
FROM
Table2)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
