asked on

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

rafrancisco

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_D

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

tomvergote

put the aggregating query in a view for easy handlingn and then query the view instead of the table for reporting

Scott Pletcher

>> 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 Pletcher

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.

Kerry Hill

ASKER

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....

rafrancisco

Yes, just change the A.ID to B.ID.

Kerry Hill

ASKER

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 Pletcher

That's what I suspected. Please give me a few minutes, working on code now ...

Scott Pletcher

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

Kerry Hill

ASKER

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?

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial