|
DECLARE @List VARCHAR(1000), @Sql VarChar(5000)
SET @List = ''
SET @Sql = ''
IF EXISTS (SELECT * FROM tempdb.sys.objects
WHERE name LIKE '#tblX%' AND type IN (N'U'))
DROP TABLE [dbo].#tblX
CREATE TABLE #tblX(MixDate DateTime, NoMixes Int, MixPlant_Name VarChar(50) )
INSERT [#tblX]
SELECT DISTINCT(mp.MixDate), SUM(mp.NoMixes) As NoMixes, Mixplant.MixPlant_Name
FROM MixProduction mp
INNER JOIN MixPlant
ON mp.MixPlant_ID = Mixplant.ID And MixPlant.IsActive =1
WHERE mp.MixDate BETWEEN '2/1/2011' AND '2/28/2011'
Group By Mixplant.MixPlant_Name, mp.MixDate
DECLARE @tblY TABLE(MixPlant_Name VarChar(50))
INSERT @tblY
SELECT DISTINCT MixPlant_Name
FROM #tblX
ORDER BY MixPlant_Name
SELECT @List = ISNULL(@List, '') +
CASE WHEN ISNULL(@List,'') = ''
THEN '[' + MixPlant_Name + ']'
ELSE ',[' + MixPlant_Name + ']' END
FROM @tblY
ORDER BY MixPlant_Name
SET @Sql = 'SELECT MixDate, ' + @List + CHAR(13)
SET @Sql = @Sql + 'FROM (SELECT MixDate, NoMixes, MixPlant_Name ' + CHAR(13)
SET @Sql = @Sql + 'FROM #tblX ) As P ' + CHAR(13)
SET @Sql = @Sql + 'PIVOT (SUM(NoMixes) FOR MixPlant_Name IN (' + @List + ')) As Pvt' + CHAR(13)
Exec (@Sql)
DROP TABLE #tblX
GO
Thanks for prompting me to persist. Now to add a couple more columns to the pivot 
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Hi guys!
I'm a software developer and not very good in database designing. One day, I was asked something like this :
"For example, there is a company with a web application. One day, the database for that application is way too big, caused performance issues and others, what is the solution for that application database?"
At first, I thought that was about using multiple database with single app. But I don't know if I was right.
I want to ask that what are the solutions? If it's "multiple database" then what should I do? Using two connection to 2 database simutaneously?
I appreciate any replies. Thanks!
...................................................
............ I have nothing to lose ...............
...................................................
|
|
|
|
|
This is a large topic but some keywords you could lookup (taken this is SQL Server):
- (better) indexing
- partitioning
- using filegroups to distribute data across disks
- using well optimized views
- transferring workload to another database using different techniques to copy the data from original db (may not be possible)
- etc etc.
- and last but not least, redesign of the database
I'd say that the key thing is to try to make such modifications which are not visible to the application (expect in response time of course) so using directly multiple db's will cause large redesigns and also typically leads to different kinds of conflict situations.
|
|
|
|
|
Thank you very much, that helped me a lot  . As least I got the path to start with
|
|
|
|
|
Most importantly, have an expert review the SQL code.
At one job I had I was assigned to add some functionality to a (batch) program and noticed some rather odd SQL. I asked about it and got the reponse, "If it ain't broke, don't fix it". In my opinion it was broke so I darn well fixed it. The result was that after I changed the SQL and added the functionality the program ran in a quarter of the time (ten minutes instead of forty).
|
|
|
|
|
Indeed!
I was also adviced that, doing the database job with an expert if you are not sure about it. It's a rather safe way.
|
|
|
|
|
One common technique is vertical splitting, so you hold only the most recent records in the (main) table and create a history table for the old ones.
To achieve this you have to conditionally (based on the time frame) do a union on the history table, and also create a mechanism to shift records from your main table to the history table.
Also, you can try to (as another poster wrote) optimize / add indexes, and/or invest in a speedier DB-server ..
|
|
|
|
|
Hi all,
I'm stuck with selecting multiple conditions in same table. I'll explain in this way. I've a table called tblPackages as follows.
package id value code
------- ---- ------ ------
pak 1 | 1 | 0 | 58
pak 2 | 3 | 0 | 58
pak 3 | 4 | 0 | 58
pak 1 | 3 | 10 | 31
pak 2 | 2 | 100 | 31
pak 3 | 3 | 8 | 31
so I want to find all the package details that id = 1 / code = 58 and id = 3 / code = 31. So the package should be 'pak 1'
I can explain the same like this. Say I execute the following query
select * from tblPackages where id = 1 and code = 58, the result is
package id value code
------- ---- ------ ------
pak 1 | 1 | 0 | 58
and then I execute the following query,
select * from tblPackages where id = 3 and code = 31, the result is
package id value code
------- ---- ------ ------
pak 1 | 3 | 10 | 31
pak 3 | 3 | 8 | 31
so the common result is,
package id value code
------- ---- ------ ------
pak 1 | 1 | 0 | 58
pak 1 | 3 | 10 | 31
That's what I want to get. From that later I want to get either code of 58 data or code of 31 data.
Can anyone of you comment on me. I've try to get data separately and merge manually, but it's not easy as i though.
thanks
I appreciate your help all the time...
CodingLover
|
|
|
|
|
It is not clear that what is your problem. As i understood your problem is to view both results of those query in a single output. To that you have to use 'UNION'.
select * from tblPackages where id = 1 and code = 58
UNION
select * from tblPackages where id = 3 and code = 31
It'll produce this output:
package id value code
------- ---- ------ ------
pak 1 | 1 | 0 | 58
pak 1 | 3 | 10 | 31
You have to use 'DISTINCT' to eliminate duplicate entries.
You can also try using JOIN but I can't understand what is you problem.
|
|
|
|
|
Hi,
You didn't quite specify what relates those to rows so I assume it's the package. In that case could have something like:
SELECT *
FROM tblPackages main
WHERE Id IN (1,3)
AND Code IN (58, 31)
AND Package = (SELECT DISTINCT Package
FROM (SELECT Package
FROM tblPackages
WHERE id = 1
AND code = 58) a,
(SELECT Package
FROM tblPackages
WHERE id = 3
AND code = 31) b
WHERE a.Package = b.Package)Don't mind about typos etc, it's not tested at all.
In the above if you can have several matching package use IN instead of equality for the package comparison. Also the statement can be simplified so this is just one version.
|
|
|
|
|
Huh? I think this[^] holds the answer, and
... WHERE (id = 1 AND code = 58) OR (id = 3 AND code = 31)
should do it.
CodingLover wrote: all the package details that id = 1 / code = 58 and id = 3 / code = 31
I guess your problem is linguistic: the "and" in the above sentence isn't really an "and" (a row couldn't have ID=1 AND ID=3 at the same time) it is more of an "and also", which actually indicates an "or" situation.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Wouldn't that lead to a situation where the row
pak 3 | 3 | 8 | 31 is included? I got the impression that it should be eliminated from the result.
|
|
|
|
|
yes it would.
however I found "so I want to find all the package details that id = 1 / code = 58 and id = 3 / code = 31. So the package should be 'pak 1'" a bit confusing, it would result in two rows IMO.
The enquirer will have to make up his mind and tell us what he really wants.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Luc Pattyn wrote: The enquirer will have to make up his mind and tell us what he really wants
That's true
|
|
|
|
|
Hi,
Is it possible to dynamically change column name in sql select statement
eg:
DECLARE @Currency NVARCHAR(50)
SET @Currency ='USD'
Select Col1 as Currency+@Currency ,col2 from tbl_Emp
|
|
|
|
|
As far as I am aware no, unless you create a dynamic sql statement.
Further Reading for dynamic SQL by Erland Sommarskog[^]
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.
|
|
|
|
|
Yup, you can change the name using an alias. Make sure that it doesn't contain spaces and stuff - Sql isn't the correct place to change the fieldname into a headername.
--edit; example added
DECLARE @Currency VARCHAR(50)
SET @Currency =' USD'
SELECT CAST(Currency AS VARCHAR) + @Currency AS [Currency]
FROM tbl_Emp
I are Troll 
|
|
|
|
|
Eddy Vluggen wrote: Sql isn't the correct place to change the fieldname into a headername
Sure it is. You should have the SQL do as much as possible and avoid having to do work in code. That's one of the functions of views.
|
|
|
|
|
Not my downvote, I appreciate sarcasm
It'd be hard to globalize, since Sql server would have to know the culture of the client.
I are Troll
|
|
|
|
|
Eddy Vluggen wrote: Not my downvote
I didn't think so.
Eddy Vluggen wrote: I appreciate sarcasm
As do I, but none was intended.
Eddy Vluggen wrote: hard to globalize
I don't see the problem, and I was making a general response to your general statement.
|
|
|
|
|
PIEBALDconsult wrote: I didn't think so.
I rather see a discussion than a mere anonymous judgement.
PIEBALDconsult wrote: As do I, but none was intended.
My apologies, but your statement does require some additional musings on your motivation. The downvote indicates that someone didn't understand your point of view, whereas they might have agreed if they could read why you take that standpoint.
PIEBALDconsult wrote: I don't see the problem
It's not "a problem"; it's an approach like many, with it's own merits. I don't like accented letters and spaces in a columnname (like Scheisse in German or Ohm in Hindi, because it messes with the report-generator and the ORM) I know that Sql Server can use long names, just like DOS moved from 8.3 to long filenames. That doesn't mean that other vendors' updated their products and also handle long names.
It's not hard to fetch a list of translated names. The endusers all have powerfull PC's that are mostly used for surfing, so I like to offload some of the work to the clients. The clients' PC knows well enough in what formatting the client his dates and doubles wants. There's also a chance that your data is being reused in multiple platforms; so, Sql Server would have to know whether it can use the full columnname "Phone number" or the abbreviated one "Phone" for the Mobile-platform.
I can also understand why you'd put as much logic as possible in your dataserver; give one the chance to update part of the logic by changing things in the server, without the need for recompilation of redistribution. You'll have another way of translating those headers; so either way it'll be a solved problem.
..now I'm curious to how you overcame said obstacles in Oracle.
I are Troll
|
|
|
|
|
PIEBALDconsult wrote: Sure it is. You should have the SQL do as much as possible and avoid having to do work in code. That's one of the functions of views.
I would say that without qualification that is not true.
If I have a requirement that an Oracle proc should produce a report file then the point of that proc is in fact dedicated to correctly producing a format that represents a 'report'. So it must of course provide headers.
But if I am writing a java/C# application whose point is to produce a report then the java code should provide the format. The java/C# code should provide the headers. Not the SQL.
And for headers that require spaces it requires a a quoted identifier on a view. I would never do that. I would rather write a view to support the data constraints and provide additional functionality that provides the actual formatting.
|
|
|
|
|
jschell wrote: I would say that without qualification that is not true.
I'm not sure what you're saying -- are you saying I should qualify my statement? I said "should ... as much as possible", that's the qualification.
jschell wrote: producing a format that represents a 'report'. So it must of course provide
headers.
Exactly.
jschell wrote: if I am writing a java/C# application whose point is to produce a report
That's generally a rather poor scheme, unless the reports are very specialized and something like Crystal (  ) won't do. For most reports a general report engine is a far better technique. I do that to output XML of complex data.
I wrote such a reporting system on my last job and there is one at my new job. Neither has the ability to alter the column headings so it must all be done in the query. The engine simply outputs the contents of a DataTable. I can add the ability to mine a couple of different ways.
|
|
|
|
|
My upvote, I like explanation
PIEBALDconsult wrote: I wrote such a reporting system on my last job and there is one at my new job. Neither has the ability to alter the column headings so it must all be done in the query. The engine simply outputs the contents of a DataTable.
Another good reason to do put formatted data and columnnames in the table. 
I are Troll
|
|
|
|
|
PIEBALDconsult wrote: That's generally a rather poor scheme, unless the reports are very specialized and something like Crystal ( ) won't do.
Presumably you are comparing the difference between creating a report engine versus using an existing commercial/free one.
In either case the engine itself contains the headers, while the data comes from the database.
PIEBALDconsult wrote: <layer>I wrote such a reporting system on my last job and there is one at my new job. Neither has the ability to alter the column headings so it must all be done in the query. The engine simply outputs the contents of a DataTable. I can add the ability to mine a couple of different ways.
I have written report engines and used several others including Crystal.
Whether the headers need to be modified (presumably by the user) has nothing to do with where they originate.
Nor does it have anything to do with what customizable features are presented to the user.
What is has to do with is separation of concerns. Your argument could lead to the supposition that one should use quoted identifiers for most tables because most tables will be used in a report and so one might as well start with the report header name.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
