|
I have a table in SQL Server 2008 like this:
EventID UserID State
1 0987 A
2 0987 A
3 0987 B
4 0956 A
5 0987 A
6 0019 A
7 0019 B
8 1289 B
9 1289 B
10 0019 A
I want to get some results from this table in such form:
UserID StateCount_of_A StateCount_of_B
0987 3 1
0019 2 1
1289 0 2
0956 1 0
I have try a few days, but I could get a disappointed result for ony one UserID. My result likes this one, for example with UserID 0987:
StateCount_of_A 3
StateCount_of_B 1
Thanks for anyone who could give me some tip.
|
|
|
|
|
Try ...
Select EventID,(select count(*) from <myeventtable> E2 where E1.EventID = E2.EventID) As Count_of_A, (select count(*) from <myeventtable> E3 where E1.EventID = E3.EventID) As Count_of_B,
from <myeventtable> E1
Remeber to Vote. 
|
|
|
|
|
You seemed to have picked up a stalker or someone objects to the unformatted code or you are getting a reaction to your sig!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I haven't tried this, however my first try would look like this:
SELECT DISTINCT UserID, StateCount_of_A, StateCount_of_B FROM tablename AS T1
LEFT JOIN (SELECT UserID, COUNT(*) AS StateCount_of_A FROM tablename AS TA WHERE State='A') ON TA.UserID = T1.UserID
LEFT JOIN (SELECT UserID, COUNT(*) AS StateCount_of_B FROM tablename AS TB WHERE State='B') ON TB.UserID = T1.UserID
ORDER BY UserID
(Actually I would try a subset of it with a single state first, then expand for the exact requirements).
The fundamental trick is to use the same table more than once, and give each "instance" a different name using the AS keyword.
And then one might have to throw in a couple of COALESCE functions to replace null counts by zeroes.
|
|
|
|
|
SELECT UserId
,SUM(StateA) StateCount_of_A
,SUM(StateB) StateCount_of_B
FROM (
SELECT UserId
,CASE WHEN [State]='A' THEN 1 ELSE 0 END StateA
,CASE WHEN [State]='B' THEN 1 ELSE 0 END StateB
FROM Junk
) T
GROUP BY UserID
|
|
|
|
|
Hello,
I cannot register new server in SQL Server 2008. I can save it but cannot start all the items (Start, Stop, ...) under Service Control menu appear disabled.
|
|
|
|
|
Kujtim Hyseni wrote: I cannot register new server in SQL Server 2008
What does that mean exactly?
Are you saying that you INSTALLED sql server and start is not enabled in the windows services control panel applet?
|
|
|
|
|
No, my 'base' or don't know how to call it server works fine and I am connecting to it as usual via 'Microsoft SQL Server Management Studio'. But when I enter to 'Microsoft SQL Server Management Studio' and try to register new server i.e. from the menu I choose from menu 'Register...' then appears the form 'New Server Registration' I cannot save this new server but cannot start it.
|
|
|
|
|
When you went to the register dialog did you hit 'test'?
If you did and it worked then can you log into the other box and start and stop the server from there? (Presumably you are using windows authentication.)
|
|
|
|
|
Yes I hit 'test' but I got the following message:
-------------------
Testing the registered server failed. Verify the server name, login credntials, and database, and then click Test again.
Additional information:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40- Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
Yes I am using Windows authentication (in fact I can login via 'sa' also).
|
|
|
|
|
Kujtim Hyseni wrote: Yes I hit 'test' but I got the following message:
So of course that is why you cannot now interact with it.
|
|
|
|
|
|
Kujtim Hyseni wrote:
And ... ?
And what?
You registered a server to which you do not have access. Thus you cannot start it.
As to why you do not have access it could either be because of permissions or because it does not exist.
|
|
|
|
|
Right-click the icon for "Sql Management Studio", choose "Run as Administrator". That solved a similar problem I had under Sql Server 2005, should not be required for 2008 though.
Since you can connect using the 'sa', I'd assume that the configuration is correct (TCP/IP being enabled) and that you have the name of the correct instance on the server. Do you have rights to start/stop the Sql Server Service when you open the "Services" list from the Configuration Panel?
Bastard Programmer from Hell 
|
|
|
|
|
I have a db with following values :
col1 col2 col3 position
aaa rack1 1-3 3
bbb rack2 22-23 2
ccc rack3 19-20 2
Output should be
col1 col2 col3 position
aaa rack1 1-3 1
aaa rack1 1-3 2
aaa rack1 1-3 3
bbb rack2 22-23 22
bbb rack2 22-23 23
ccc rack3 19-20 19
ccc rack3 19-20 20
Basically, its taking the position and creating a row for each value from col3.
Can i do this on-fly and create a temp table? or i have to create a procedure?
thanks!!!
VK
|
|
|
|
|
If you are doing this in the database you are going to have to write some code, you can't magic it into the multiple row. I would look at the method of inserting the original record and change or enhance that method.
Alternatively you can use an insert trigger spit, I would do this as a last resort.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Try this
CREATE TABLE #Temp
(
ID INT IDENTITY(1,1),
col1 VARCHAR(50),
Col2 VARCHAR(50),
Col3 VARCHAR(50),
position VARCHAR(50)
)
CREATE TABLE #Temp2
(
col1 VARCHAR(50),
Col2 VARCHAR(50),
Col3 VARCHAR(50),
position VARCHAR(50)
)
INSERT INTO #Temp
SELECT 'aaa', 'rack1', '1-3', '3' UNION
SELECT 'bbb', 'rack2', '22-23', '2' UNION
SELECT 'ccc', 'rack3', '19-20', '2'
DECLARE @TotalRowCount INT
DECLARE @RowCount INT
DECLARE @Positon INT
DECLARE @I INT
SET @RowCount = 1
SELECT @TotalRowCount = COUNT(*) FROM #Temp
WHILE @RowCount <= @TotalRowCount
BEGIN
SELECT @Positon = Position FROM #Temp WHERE Id = @RowCount
SET @I = 1
WHILE @I <= @Positon
BEGIN
INSERT INTO #Temp2
SELECT Col1, Col2, Col3, (SUBSTRING(Col3,0,CHARINDEX('-',Col3)) + @I - 1) FROM #Temp WHERE Id = @RowCount
SET @I = @I + 1
END
SET @RowCount = @RowCount + 1
END
SELECT * FROM #Temp2
DROP TABLE #Temp
DROP TABLE #Temp2
|
|
|
|
|
hiiiiiii, I have to make a lan chatting software for windows using ADO.NET & sqlserver.The criteria is that what we enter in the chatbox should update in database & the other lan user see it & also who is online on lan.plz help & provide code
|
|
|
|
|
Member 9131839 wrote: plz help & provide code
Sorry but that is not how this forum works. However, if you try a search of the Articles[^] you will find lots of examples of chat programs and database usage that will help in developing your solution.
|
|
|
|
|
Member 9131839 wrote: I have to make
Read that part again; it conflicts with the last part of your post.
Bastard Programmer from Hell 
|
|
|
|
|
Member 9131839 wrote: provide code
No. People are not going to just give you their code they worked hard for. You have to work on your own project and when you run into trouble with a part of it, then ask for help.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi,
I am getting the above error on MySQL stored procedure..
Kindly help...
here is the error followed by the code:
1064 - You have and error in your SQL syntax. check the manual that corresponds to your MySQL server version for the right syntax to use near '
END
ELSE
BEGIN
INSERT INTO salary_slips (salary_slip_month, salary_slip_y'
at line 13
BEGIN
SET param_slip_released = FALSE;
IF EXISTS (SELECT salary_slip_id FROM salary_slips WHERE salary_slip_month = param_slip_month AND salary_slip_year = param_slip_year) THEN
BEGIN
SET param_slip_released = (SELECT salary_slip_released FROM salary_slips WHERE salary_slip_month = param_slip_month AND salary_slip_year = param_slip_year);
IF param_slip_released = FALSE THEN
BEGIN
SELECT * FROM salary_slips;
END
END
ELSE
BEGIN
INSERT INTO salary_slips (salary_slip_month, salary_slip_year) VALUES (param_slip_month, param_slip_year);
END
END;
|
|
|
|
|
jrahma wrote: VALUES (<layer>param_slip_month, param_slip_year)
I cannot see where you set the values of these parameters!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I have a list to populate. I have a query to populate it in mssql 2008. It involves three tables. If the query returns the value then I populate it and it is done. But , if the query doesnot return anything then I want to run another select statement because I do not want to leave the list view empty. I have to populate at least the names of the client. It could be something simple but I am just beginning to use mssql so I am stuck.
Select C.name as Client ,
B.Detail as Detail from tableA A
left join tableB B on B.bID = A.bID
Left join tableC C on C.clientID = B.clientID
where aID= '100';
if does not return anything,
I want to run another sql to atleast get the name of the client even if there is no related detail with that client.
second sql would be
Select C.name as Client from tableA A
Left join tableC C on C.clientID = A.bID
where aID= '100'
[P.S. the bID field of Table A could actually either be bID as is or C.clientID inserted into bID field of Table C.]
I am confident about the queries but its just about extracting the values from the result.
Thanks,
|
|
|
|
|
Look at @@RowCount[^]
Remember that it changes with the next executed statement.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
