|
|
Have you considered SQLite[^]?
I have used it a small amount and it seems fairly good as a database engine.
As an afterthought, if you write your code in a layered manner you should be ably to create something that is database agnostic. You could then experiment with different databases to see which one suits your needs.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
A local version of Sql Server, that looks/act a lot like Sql Server?
LocalDB[^]
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
At least at one time Microsoft allowed SQL Server to be used for free for smaller businesses. Figuring that out can be difficult but might be worth it.
The normal goto for free alternatives is MySQL but you should look at the license for that, but an alternative which might be even better now is one that started as a fork of MySQL which is MariaDB.
Those however are 'servers' and an embedded database might serve your application better. But you might want to make sure that your application will never have a need to have multiple clients and one server.
|
|
|
|
|
I should remember this, but my mind is drawing a blank.
In SQLServer 2008, I have a table: site, datapoint (there are other columns, but not important right now).
I would like to produce a list of datapoints and the sites they are listed with.
Example:
Site DataPoint
KC WindSpeed
KC Temp
CH WindSpeed
CH Temp
CH Power
Output:
Power CH
Temp CH KC
WindSpeed CH KC
Can someone help me with the T-SQL? I can do this with temp tables and updates, but would prefer an elegant solution.
Thanks,
Tim
|
|
|
|
|
If I've understood your question properly, you're looking to concatenate row values rather than PIVOT them.
This article[^] covers most of the options. For example, the black-box XML method:
SELECT
DataPoint,
STUFF
(
(
SELECT ',' + Site
FROM YourTable As I
WHERE I.DataPoint = O.DataPoint
ORDER BY Site
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, ''
) As Sites
FROM
YourTable As O
GROUP BY
DataPoint
;
http://sqlfiddle.com/#!3/5657e6/3/0[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you, but, no. Not concatenation.
I need the CH and KC to be the column headers.
What I have is 1400 data points and 4 sites; in theory, all sites SHOULD have the same datapoints, but... they don't.
I need to create a matrix of datapoints and sites to show what is there and what isn't.
What I've presented is the simplified version, but the concept is correct.
|
|
|
|
|
So if CH and KC are the column headers, what are the column values?
Remember, you can't have different columns for different rows in the same resultset, which is what the "output" section of your question seems to be doing.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Either the column name or the datapoint or 'Exists'; any indicator to show the datapoint exists for that site, but if it doesn't exist, a blank value.
|
|
|
|
|
So a basic dynamic pivot then?
DECLARE @cols As nvarchar(max), @query As nvarchar(max);
SET @cols = STUFF
(
(
SELECT DISTINCT ',' + QUOTENAME(Site)
FROM Source
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)')
, 1, 1, ''
);
SET @query = N'SELECT DataPoint, ' + @cols + N' FROM Source PIVOT (COUNT(Site) FOR Site IN (' + @cols + N')) As p';
EXEC(@query);
http://sqlfiddle.com/#!3/5657e6/5/0[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you.. yes, that worked... like I said, I used to use it, but forgot how.
|
|
|
|
|
Could you help with XML select. Below is my xml however it won't select correct value.
DECLARE @input AS XML
SET @input =N'<?xml version="1.0" encoding="utf-16"?><NewDataSet><GroupID>111111</GroupID><GroupID>111111</GroupID><GroupID>999999</GroupID><GroupID>777777</GroupID></NewDataSet>'
SELECT
Ppl.GroupID.value('(@GroupID)', 'VARCHAR(6)') AS 'GroupID'
FROM
@input.nodes('/NewDataSet/GroupID') as Ppl(GroupID)
|
|
|
|
|
I do not understand what do you mean by (@GroupID), but you should write it like this:
Ppl.GroupID.value('.', 'VARCHAR(6)') AS 'GroupID'
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
for example
Abul
Azar
Mohamed
Sheik
vazith
i want to select names from azar to last record of the table
|
|
|
|
|
SELECT *
FROM Table
WHERE Name >= 'Azar'
For this query to be efficient, the table should be indexed on the name column
|
|
|
|
|
use PARA2;
with pel(reg,inst,nama,alamat,sts)
as (select p.ID_Pelanggan
,p.No_Pelanggan
,p.Nama_Pelanggan
,p.Alamat
,case when p.Status_Pelanggan=2 then 'Aktip'
else 'Non Aktip'end'Status'
from PELANGGAN p),
ganti(reg,ml,nml,mb,nmb,thn)
as (select g.ID_Pelanggan
,g.Meter_Lama
,g.Nomor_Lama
,g.Meter_Baru
,g.Nomor_Baru
,g.Tanggal
from GANTI_Log g)
select p.reg
,p.inst
,p.nama
,p.alamat
,p.sts
,g.ml
,g.nml
,g.mb
,g.nmb
,g.thn
from pel p left outer join ganti g
on p.reg=g.reg
order by p.reg
|
|
|
|
|
What do you mean with "last row" - is it the row with newest date (g.Tanggal), and what would you like to select if no entry is present yet?
What about
...
as (select TOP 1 g.ID_Pelanggan
,g.Meter_Lama
,g.Nomor_Lama
,g.Meter_Baru
,g.Nomor_Baru
,g.Tanggal
from GANTI_Log g
Order by g.Tanggal desc
)
...
|
|
|
|
|
Bernhards solution is valid for SQLServer.
But what if you don't use SQL Server?
|
|
|
|
|
If he is using HAL then he should have asked for HAL9000 compatible SQL 
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
"I'm sorry, Eddy. I'm afraid I can't do that."
|
|
|
|
|
Hello Experts,
I have a table ID, Lock and other fields. I have a VB.NET program that gets the TOP 1 record that is not lock and lock that record to be keyed/entry. Multiple users will use this program, is it possible that more than one user can access the same record in this process? Its possible users will request a record at the same time.
Thanks a lot.
Revision
Table
ID | Lock |
------------
1 | False |
2 | False |
3 | False |
Lets say that 2 computer are using same program connecting to the same server database
Client:1 - Request Time: 10:30:00 will get the ID 1, ID 1 will lock(update to true)
Client:2 - Request Time: 10:30:05 will get the ID 2, ID 2 will lock(update to true) - cause ID 1 is lock
This is my question. same request
Client:1 & 2 - Request Time: 10:30:00
- Query is (SELECT TOP 1 * FROM Table WHERE Lock = False)
- Put ID to a variable
- In the above run time, Is the 2 client will get the ID 1 or the this query will stack on SQL Server will finish one query first before executing another query?
- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind.
- Most of the problem in your life are due to two reasons: you act without thinking, or think without acting
modified 29-Sep-14 9:34am.
|
|
|
|
|
I don't understand your question. How are you locking the record?
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
I think you will need to write your own locking mechanism. No big deal.
|
|
|
|
|
hansoctantan wrote: is it possible that more than one user can access the same record in this
process?
Probably. Does not mean that it will be a problem.
There's a lot of stuff written on locking in SQL, with topics like lost updates[^] and dirty reads (no, not those magazines). You can add various hints[^] to the query. MSDN has dedicated a section[^] to consistency and concurrency.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
What business case do you have that requires you to lock it?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
