April 8, 2011 at 8:58 am
Hi,
I have a table named Test1, this table as the following columns:
Name; Number;Status.
Here is some example of it's data:
Pedro; 1; A
Pedro;2;C
Martha;7;A
Martha;8;C
Martha;9;C
The data inside the column number is unique (don't have two identical values)
Status is always 'A' or 'C'
I what to create a view that returns in the left side the number of the person with status ='c' and on it's right side the number of that person where the status is 'A'.
E.G:
2| C | 1|A
8|C | 7|A
9|C | 7 |A
Can someone help with this query? thank you
April 8, 2011 at 9:14 am
Hi river1
Strange question! Here's some help:
DECLARE @People TABLE(
FirstName VARCHAR(10),
SomeKey TINYINT PRIMARY KEY,
SomeForeignKey CHAR(1)
)
INSERT @People
SELECT 'Pedro', 1, 'A' UNION ALL
SELECT 'Pedro', 2, 'C' UNION ALL
SELECT 'Pedro', 3, 'A' UNION ALL
SELECT 'Martha',7, 'A' UNION ALL
SELECT 'Martha',8, 'C' UNION ALL
SELECT 'Martha',9, 'C'
SELECT
A.SomeKey,
B.Nb
FROM
@People AS A
CROSS APPLY
(
SELECT
Nb = COUNT(*)
FROM
@People AS B
WHERE
A.FirstName = B.FirstName
AND
B.SomeForeignKey = 'A'
) AS B
WHERE
SomeForeignKey = 'C'
See how I posted the test data as a script, this will help people help you by making it easier to just copy paste your code and propose a solution.
Let me know how it goes!
Maxim
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply