May 27, 2014 at 12:46 am
Hi all,
I hope someone can assist me with this need:
We have four balls named 1,2,3,4.
We have tests that pull the balls one after the other.
Summarize table indicates which ball came out in every pull.
TestFirstPullSecondPullThirdPullForthPull
12341
22413
31432
43214
53421
Here is the create table script:
CREATE TABLE #Test
(
Test INT,
FristPull INT,
SecondPull INT,
ThirdPull INT,
ForthPull INT
);
INSERT INTO #Test(Test,FristPull,SecondPull,ThirdPull,ForthPull)
VALUES(1,2,3,4,1),(2,2,4,1,3),(3,1,4,3,2),(4,3,2,1,4),(5,3,4,2,1)
i need to supply a matrix that will express how many times ball1 was pulled first, second, etc.
and the same for every ball:
CountTimesBall1Ball2Ball3Ball4
FirstPull1220
SecPull0113
ThirdPull2111
ForthPull2111
Thanks in Advance.
May 27, 2014 at 1:35 am
Something like this?
😎
USE tempdb;
GO
DECLARE @test-2 TABLE
(
Test INT NOT NULL
,FristPull INT NOT NULL
,SecondPull INT NOT NULL
,ThirdPull INT NOT NULL
,ForthPull INT NOT NULL
)
INSERT INTO @test-2(Test,FristPull,SecondPull,ThirdPull,ForthPull)
VALUES(1,2,3,4,1),(2,2,4,1,3),(3,1,4,3,2),(4,3,2,1,4),(5,3,4,2,1)
;WITH BALLS(NUM) AS
( SELECT NUM FROM (VALUES(1),(2),(3),(4)) AS X(NUM))
SELECT
B.NUM
,SUM(CASE WHEN T.FristPull = B.NUM THEN 1 ELSE 0 END) AS T_FristPull
,SUM(CASE WHEN T.SecondPull = B.NUM THEN 1 ELSE 0 END) AS T_SecondPull
,SUM(CASE WHEN T.ThirdPull = B.NUM THEN 1 ELSE 0 END) AS T_ThirdPull
,SUM(CASE WHEN T.ForthPull = B.NUM THEN 1 ELSE 0 END) AS T_ForthPull
FROM BALLS B
OUTER APPLY @test-2 T
GROUP BY B.NUM
Results
NUM T_FristPull T_SecondPull T_ThirdPull T_ForthPull
----------- ----------- ------------ ----------- -----------
1 1 0 2 2
2 2 1 1 1
3 2 1 1 1
4 0 3 1 1
May 27, 2014 at 2:41 am
Hi Eirikur,
The columns and rows are opposite, but it is still great - I will use it.
Thanks a lot 🙂
Arik.
May 27, 2014 at 4:23 am
TommyF (5/27/2014)
Hi Eirikur,The columns and rows are opposite, but it is still great - I will use it.
Thanks a lot 🙂
Arik.
Just do a cross tab.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply