What is the best way to normalize this data

I have some data that I want to report on, the problem is, it is not in a great format for how I want to look at it.

I have a table called RIR:

ID     Type    Number
2       10       450
2       10        500
2       10        550
2       7          1000
2       8          1200
What I would like to do is format this data so that I associate the records of type 7 and 8 with each of the type 10s.  I am stuck with the data as it is, there is no way to change it.

Result:
ResultID (should be the number of the type 10 record so I can group them together in a report)

ResultID      ID     Type     Number
450           2       10        450
450           2        7         1000
450           2        8         1200
500           2       10         500
500           2        7         1000
500           2        8         1200
550           2       10         550
550           2        7         1000
550           2        8         1200



kerryhillAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rafranciscoCommented:
Try this:

SELECT Number AS ResultID, Type, Number, Number
FROM RIR
WHERE Type = 10
UNION ALL
SELECT A.Number AS ResultID, A.ID, B.Type, B.Number
FROM
(SELECT ID, Type, Number
FROM RIR
WHERE Type = 10) A CROSS JOIN
(SELECT ID, Type, Number
FROM RIR
WHERE Type IN (7,8)) B
Michael_DCommented:
Try this:

SELECT Number AS ResultID, Type,  Number
FROM RIR
WHERE Type = 10
UNION ALL
SELECT A.Number AS ResultID,  B.Type, B.Number
FROM
(SELECT ID, Type, Number
FROM RIR
WHERE Type = 10) A CROSS JOIN
(SELECT ID, Type, Number
FROM RIR
WHERE Type IN (7,8)) B
ORDER by
ResultID,  Type desc
tomvergoteCommented:
put the aggregating query in a view for easy handlingn and then query the view instead of the table for reporting
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Scott PletcherSenior DBACommented:
>> What I would like to do is format this data so that I associate the records of type 7 and 8 with each of the type 10s. <<

Is this also by ID, or is the ID always constant?  For example, should this input:

ID     Type    Number
2       10       450
2       10        500
2       10        550
2       7          1000
2       8          1200
3       10        301
3       10        302
3       7          3700
3       8          3800

Yield your original results of 9 ID = 2 rows and 6 ID = 3 rows <or> many more rows because *every* type 10, regardless of ID, is matched to every other type 7/8 row?
Scott PletcherSenior DBACommented:
Btw, if this is being done just as an intermeditate step for reporting, it's very possible that you could just produce the report directly from the original table, which if possible would, of course, be much more efficient.
kerryhillAuthor Commented:
That works, but I need the ID from the 7,8 row is the original ID, should I select B.ID rather than A.ID?

               ID             Type         Number
      2      10              450      
      2      10              500      
      2      10              550      
      2      7               1000      
      2      8               1200      
      3      10              450      
      3      10              500      
      3      10              550      
      3      7               1000      
      3      8               1200      

Result:
ResultID          ID           Type        Number
450                2              10           450
450                2               7            1000
450                2               8            1200
450                3              10           450
450                3               7            1000
450                3               8            1200

and so on....
rafranciscoCommented:
Yes, just change the A.ID to B.ID.
kerryhillAuthor Commented:
ScottPletcher:
Yield your original results of 9 ID = 2 rows and 6 ID = 3 rows <or> many more rows because *every* type 10, regardless of ID, is matched to every other type 7/8 row?

The non-type 10 rows should be duplicated and associated with each type 10 row with the same ID other IDs should not be matched together.
Scott PletcherSenior DBACommented:
That's what I suspected.  Please give me a few minutes, working on code now ...
Scott PletcherSenior DBACommented:
OK, this still needs a little more tuning, but I think it's working at least :-) :


SELECT DISTINCT r10.number AS [ResultID],
      CASE WHEN whichToProcess = 10 THEN r10.id ELSE r78.id END AS [ID],
      CASE WHEN whichToProcess = 10 THEN r10.type ELSE r78.type END AS [Type],
      CASE WHEN whichToProcess = 10 THEN r10.number ELSE r78.number END AS [Number]
FROM RIR r10
INNER JOIN RIR r78 ON r78.type IN (7, 8) AND r78.id = r10.id
CROSS JOIN (
      SELECT 10 AS whichToProcess UNION ALL SELECT 78
) AS whichToProcess
WHERE r10.type = 10
kerryhillAuthor Commented:
Very cool, it does work well.  So any field that I want to add to this i would have to throw into the case statement?
Scott PletcherSenior DBACommented:
Yep.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.