Help on query

• river1

  SSC-Insane

  Points: 24128

  More actions

  April 8, 2011 at 8:58 am

  #234675

  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

• Maxim Picard

  SSCrazy

  Points: 2052

  More actions

  April 8, 2011 at 9:14 am

  #1309242

  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