Link to home
Start Free TrialLog in
Avatar of rquirion
rquirionFlag for Canada

asked on

Return multiple rows from a value in another column

I have this

teamID    someValue

7              2
8              3

I want this

7              1
7              2
8              1
8              2
8              3

I want to return multiple rows based on the value in column "somevalue"
ASKER CERTIFIED SOLUTION
Avatar of deighton
deighton
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
you may eventually need a


;WITH CTE AS (SELECT MAX(SomeValue) as X FROM YourTable
                        UNION ALL
                        SELECT X - 1 FROM CTE WHERE x>1)
                       
      SELECT E.SomeValue TeamId, C.X YourNewValues FROM YourTable E
       JOIN CTE C ON C.X <= E.SomeValue
       ORDER BY E.SomeValue , C.x
       option(maxrecursion 0)
try this




SELECT DISTINCT A.ID, A.Code, A.ownerName, B.Count
FROM Team A
JOIN (
  SELECT COUNT(*) as Count, B.Code
  FROM team B
  GROUP BY B.Code
) AS B ON A.Code = B.Code
WHERE B.Count > 1
ORDER by A.Code;
Avatar of rquirion

ASKER

worked perfect. Thank you