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
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.
,CASE WHEN [State]='A' THEN 1 ELSE 0 END StateA
,CASE WHEN [State]='B' THEN 1 ELSE 0 END StateB
GROUP BY UserID
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.
Testing the registered server failed. Verify the server name, login credntials, and database, and then click Test again.
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).
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?
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
CREATE TABLE #Temp
ID INT IDENTITY(1,1),
CREATE TABLE #Temp2
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
SELECT @Positon = Position FROM #Temp WHERE Id = @RowCount
SET @I = 1
WHILE @I <= @Positon
INSERT INTO #Temp2
SELECT Col1, Col2, Col3, (SUBSTRING(Col3,0,CHARINDEX('-',Col3)) + @I - 1) FROM #Temp WHERE Id = @RowCount
SET @I = @I + 1
SET @RowCount = @RowCount + 1
SELECT * FROM #Temp2
DROP TABLE #Temp
DROP TABLE #Temp2
Last Visit: 31-Dec-99 18:00 Last Update: 27-Sep-23 3:04