|
Greetings All,
I have a particular problem that I am hoping someone with some TSQL know-how can assist me with.
Here's the issue:
I have a table that contains 40 some-odd columns. the first 2 columns are essentially identifiers. The following columns are components of the identifier. To give a clear picture, lets' say the first row has CHCK in the 1st column and Chocolate Cake in the 2nd column. The rest of the columns contains the ingredients of the Chocolate Cake (let's say 7 ingredients). The next row is for Grilled Cheese. This row would only have 3 ingredients.
Here is what I would like to achieve:
I want to run through each row and read every column to see if there is a value in it. If there is a value, I would like to put the identifier and the ingredient in another table with an insert so that this new table (with only 2 columns) looks something like this
CHCK eggs
CHCK milk
GCHEES bread
GCHEESE cheese
Is there a way that I could do this in a stored procedure? I know that I could use a cursor to the rows but each row could have a variable number of *ingredients* and that is where I am lost.
Thank you in advance for any suggestions you may offer.
|
|
|
|
|
please don't explain table data in words. Please show us that in tabular format so that we can have a clear steer view
|
|
|
|
|
STOP - you are making me sick, this is the WORST design for a table you can possibly have. I STRONGLY suggest you chuck this table out and design a correct data structure. Something like:
Recipie
Ingredients
Where ingredients has a 1 to many relationship with the recipie field. If you are just starting then do some reading on data structures, database design before you continue. This is a typical clusterf**k created by someone who has no idea what they are doing. Do some research and get it right, otherwise you will waste a lot of your time attempting to undo these stupid structures.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: STOP - you are making me sick, this is the WORST design for a table you can possibly have. I STRONGLY suggest you chuck this table out and design a correct data structure. Something like:
Recipie
Ingredients
I think that is what he wants to do.
"Lots of programmers have had sex - some have even had it with members of the same species." - Pete O'Hanlon
|
|
|
|
|
select column1,convert(varchar(20),isnull(column2,''))+''+convert(varchar(20),isnull(column3'')) as coluumn2 from tablename
use the above query and write like above for all the ingredent column .
lets me know if any things is needed.
|
|
|
|
|
sorry for the above reply
use this
select coumun1,column2 from table1
where column2 is not null
union all
select coumun1,column3 from table1
where column3 is not null
union all
select coumun1,column4 from table1
where column4 is not null
here for three column u have to write for all column
|
|
|
|
|
for sqlserver 2005
use unpivot method
|
|
|
|
|
Whoa, Whoa guys. I may not have explained myself clearly from the responses I have received.
The database tables are in the correct format as you have suggested. I need a query to pull the data in the format I mentioned to another party. I am not certain how they will be utilizing the data in this manner but that what has been spec'd out to me.
Thank you Anup for your assistance and I will further explore the command you have suggested.
|
|
|
|
|
Hi,
I'm looking for a SQL-based reporting system with the following features for running against a SQL Server 2005 database.
- SQL for each report is to be hand coded (i.e. NOT 'ad-hoc/drag-n-drop end user reporting')
- Users will select parameters at run time
- Output to browser (don't care about controlling hard-copy formatting)
- Must be able to include complete T-SQL.
- Ideally include support for transactional operations
I wrote something very much like this in my last job, but would rather buy this time.
Anyone used or even know of such a beast? I've scoured Google with no result. Everything I've found is either 'Build reports without knowing SQL' or 'use this toolkit to integrate a specific report into your application.
Thanks,
Max.
|
|
|
|
|
It's called SQL Server Reporting Services. DevExpress also do an excellent reporting tool.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi There
I need to create report that needs to group by "[B]status[/B]= Funded and Not funded" , "[B]Institute[/B]" body is title
I need to find the [B]Total amount [/B] that Institute had earned for different Research Proposals. In One Proposal there could be Group of people involved
I am creating Report using this Query
SELECT Proposal.Title, Agency.AID, Proposal.Type, Role.PersonID, Role.RoleTypeID, Proposal.SID, Div.IID, Institutions.IName, Institutions.Regional, Proposal.Amount, Proposal.DateSubmitted
FROM ((Proposal LEFT JOIN Agency ON Proposal.PID = Agency.PID) LEFT JOIN (Institutions RIGHT JOIN Div ON Institutions.IID = Div.IID) ON Proposal.PID = Div.PID) LEFT JOIN (Contact RIGHT JOIN Role ON Contact.PersonID = Role.PersonID) ON Proposal.PID = Role.PID
WHERE (((Role.RoleTypeID)=3 Or (Role.RoleTypeID)=4 Or (Role.RoleTypeID)=6) AND ((Proposal.SID)=1 Or (Proposal.SID)=2) AND ((Institutions.Regional)=Yes)) OR (((Role.RoleTypeID)=3 Or (Role.RoleTypeID)=4 Or (Role.RoleTypeID)=6) AND ((Proposal.SID)=1 Or (Proposal.SID)=2) AND ((Institutions.IName) Like "*TEES*" Or (Institutions.IName) Like "*COE*"));
My result is some thing like this
Title Inst Type Person Role SID Amount
A Del ATE Lee PI Funded 500,000
A Del ATE Juli PI Funded 500,000
A Del ATE Kris Co-PI Funded 500,000
B Del NSF Kris PI Funded 900,000
B Del NSF Kris PI Funded 900,000
B Del NSF Kris co-PI Funded 900,000
c comp ATE Kris PI N-Funded 1,200,000
c comp ATE Kris PI N-Funded 1,200,000
I want the total By Inst = Del = 14,00,000
and Inst= comp = 1,200,000
I have created the group by Institute, added text box at the footer of Institute and assigned = Sum([Amount]) to the control source of that text box. I am not sure Why I am not getting the result
Thank you
Bijaya
|
|
|
|
|
You are not Using Group By method.For your knowledge U should Use Group By Query.
Like : SELECT A.CustomerName, A.SUM(OrderPrice) FROM Sales as A GROUP BY A.CustomerName
Best Regard
Anubhava Dimri
mailto: anubhava.prodata@gmail.com
|
|
|
|
|
Thank you Anubhava,
This is my Group By Query :
SELECT Proposal.Title, Agency.AID, Proposal.Type, Role.PersonID, Role.RoleTypeID, Proposal.SID, Div.IID, Institutions.IName, Institutions.Regional, Sum(Proposal.Amount) AS SumOfAmount, Proposal.DateSubmitted
FROM ((Proposal LEFT JOIN Agency ON Proposal.PID = Agency.PID) LEFT JOIN (Institutions RIGHT JOIN Div ON Institutions.IID = Div.IID) ON Proposal.PID = Div.PID) LEFT JOIN (Contact RIGHT JOIN Role ON Contact.PersonID = Role.PersonID) ON Proposal.PID = Role.PID
GROUP BY Proposal.Title, Agency.AID, Proposal.Type, Role.PersonID, Role.RoleTypeID, Proposal.SID, Div.IID, Institutions.IName, Institutions.Regional, Proposal.DateSubmitted
HAVING (((Role.RoleTypeID)=3 Or (Role.RoleTypeID)=4 Or (Role.RoleTypeID)=6) AND ((Proposal.SID)=1 Or (Proposal.SID)=2) AND ((Institutions.Regional)=Yes)) OR (((Role.RoleTypeID)=3 Or (Role.RoleTypeID)=4 Or (Role.RoleTypeID)=6) AND ((Proposal.SID)=1 Or (Proposal.SID)=2) AND ((Institutions.IName) Like "*TEES*" Or (Institutions.IName) Like "*COE*"));
Still result remains the same
Title Inst Type Person Role SID SumOfAmount
A Del ATE Lee PI Funded 500,000
A Del ATE Juli PI Funded 500,000
A Del ATE Kris Co-PI Funded 500,000
B Del NSF Kris PI Funded 900,000
B Del NSF Kris PI Funded 900,000
B Del NSF Kris co-PI Funded 900,000
c comp ATE Kris PI N-Funded 1,200,000
c comp ATE Kris PI N-Funded 1,200,000
At Institute footer cntains Text feild =sum(SumOfAmount) for Del= ($500,000 *3) + ($900,000 * 3) = $ 4200,000
for comp= $2400,000
Where As I want this
By Inst = Del = 14,00,000
and Inst= comp = 1,200,000
Thank you
Bijaya
|
|
|
|
|
Hi Everybody,
I am working on the proble With the first query. I am not sure why I am not getting the Sum Amount by Institute(Inst)
Just to get Total Amount by Institute. I created another Query that is:
SELECT Institutions.IName,
Sum(Proposal.Amount) AS SumOfAmount,
Proposal.sid
FROM Proposal INNER JOIN (Institutions INNER JOIN Div ON Institutions.IID = Div.IID) ON Proposal.PID = Div.PID
GROUP BY Institutions.IName, Proposal.sid
HAVING (((Proposal.sid)=1 Or (Proposal.sid)=2));
Datasetview of the above Query is:
Inst SumAmount SID(status)
Del 14,00,000 funded
comp 12,00,000 not-funded
But when I assign this to Combo box at the Footer of Institute Group. I am not getting the accepcted result. I am getting this one.
Del = 500,000
Comp = 1200,000
Could onyone help me why this is happening?
I am counting on you, my project is due comming Monday. help Needed!
thank you
Bijaya 
|
|
|
|
|
I'm new to crystal report and currently is using CR Ver. 10 to develop my project. I would like to know how can i make my report to display data from db in horizontal with maximum of 3 rows of data per page.
My sql command look something as below:
select personid, personname, personfname, personlname, personmname
from person_tab
It should display as template below:
PERSON REPORT
ID | ID 1 | ID 2 | ID 3
NAME | NAME 1 | Name 2 | Name 3
FIRST NAME | First Name 1 | First Name 2 | First Name 3
LAST NAME | Last Name 1 | Last Name 2 | Last Name 3
MID NAME | Mid Name 1 | Mid Name 2 | Mid Name 3
|
|
|
|
|
you should used cross tab report type . it will available when u will add the report in own solution explorer.
lets me know if any things is needed.
|
|
|
|
|
Hi Experts
I facing a great Problem Pls Help.
I Have Database in SQL Server 2005.i giving it to My Client end.
i Want To Protect My Database Not To Open or Modified By Another User.
How To Make Certificate of Database and Use It at Client End So Another Person Not To Open My Database
|
|
|
|
|
I don't think that you can sign a SQL-database the way you sign a Word-document. A database is meant to be changed, if you want to restrict access then dive into permissions, users, passwords, policies and logons 
I are troll
|
|
|
|
|
Hi
I am using Visual Studio 2005 and C# to develop a Windows application in the cancer department where I work. It is a small patient management system. I was asked to use MS Access as the backend. For obvious reasons I would prefer to use SQL Server. Unfortunately the network guys running the hospital network are very sticky with what is allowed on their servers. This is quite understandable, but it does make things a bit difficult. We are only allocated a folder on the network that our department has access to. With MS Access it is easy enough. We just copy the MS Access database into the folder and point the application to it.
If I can use SQL Server how would I deploy it? Must SQL Server be installed on the server first? I will probably use SQL Server 2005 Express.
Thanks.
Kobus
|
|
|
|
|
SQL server must be installed on the Server and you continue as usuall and point to SQl that Access and that means your Code will change in the DAL , you will be using sqlclient not oledb
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
www.ITS.co.za
|
|
|
|
|
Your'e network people are idiots (or have strict policies about what they can allow departments) . You need some arguments that will move them off this policy position or get an exception. They will have some database they use and may allow you to use part of it, may be oracle or one of the other enterprise DBs.
Our network team for instance will not allow Access anywhere near a server, it has been known to crash a server and they are terrified of it. Unless properly managed it can grow horrendiously. Are you sure you do not need to install the jet drivers or even office on the server? This would be a good argument against Access.
Good luck.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello everyone,
I've a exe file in C:\. I need to run it from stored proc using xp_cmdshell. Please note that I'm using SQL Server 2000.
My stored proc is :
Create proc callExe
As
EXEC master.dbo.xp_cmdshell 'C:\PrintToPDFConsole.exe'
After executing this, the output is :
NULL
Unhandled Exception: System.ComponentModel.Win32Exception: No application is associated with the specified file for this operation
at PrintToPDF.Program.Main(String[] args)
NULL
and the exe file did not run.
Please help in this.
Also, I've tried with common exe file like 'notepad.exe' like:
ALTER proc callExe
As
EXEC master.dbo.xp_cmdshell 'notepad.exe'
When I executed this, the execution is going on and not showing any output. The execution time is more that 10 mins and going on...
Then I forcefully closed the isqlw.exe from Task Manager.
Is that mean exe file can't be executed in sql server 2000 using xp_cmdshell ?
--Krushna Chandra Sahu
prfkrushna@gmail.com
|
|
|
|
|
You cannot run exe which has GUI. You can run exe which is running in background process.
|
|
|
|
|
Yes, I forgot to write there that the exe file does not have a GUI.
It will automatically convert the any file to pdf file and save to My Document folder.
--Krushna Chandra Sahu
prfkrushna@gmail.com
|
|
|
|
|
Hello,
My Question is related to SQL Server Memory Usage,
I have a VB.net application with MSDE.
When i run my application, the sqlserver.exe in Task Manager uses memory of about 20 MB,
Then when I View any Crystal Report created report, the memory goes on increasing as the report is loaded and if reaches about 150 to 200 MB, but even after closing the report and also closing the application, SQL Server does not release memory.
So when we again start the application, again memory goes on accumulating but is never released.
Memory is released only when we Stop and start the SQL Server or restart the PC.
Can anyone tell me how to make SQL Server Release Memory from my VB.net application.
Thanks in Advance.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
