2

in tsql how can I turn this sample table:

ID  FIELDNO ROWNO VALUE
ABC   2     1     Cat1Val1
ABC   2     2     Cat1Val2
ABC   2     3     Cat1Val3
ABC   3     1     Cat2Val1
ABC   3     2     Cat2Val2
ABC   5     1     Cat3Val1

to a table that will create three columns based on the fieldno and duplicate the rows so that it lists all possible variations of whatever fieldno has the highest rowNo?

So fieldno 2 will become CAT1, 3 -> CAT2 and 5 -> CAT3

Expected result:

   ID  CAT1     CAT2     CAT3
   ABC Cat1Val1 Cat2Val1 Cat3Val1
   ABC Cat1Val1 Cat2Val2 Cat3Val1
   ABC Cat1Val2 Cat2Val1 Cat3Val1
   ABC Cat1Val2 Cat2Val2 Cat3Val1
   ABC Cat1Val3 Cat2Val1 Cat3Val1
   ABC Cat1Val3 Cat2Val2 Cat3Val1

I could then use this as a base to join with other tables.

Here's a fiddle with more data.

I've tried to create some CASE WHEN clauses but I think this is not going to work.

Hope you can show me a way how this can be solved.

Thank you.

Improve this question
1
  • 1
    Can you explain why you need the data in this format? It seems highly unusual that you would first denormalize in this way and then join to other tables. Commented Feb 13, 2014 at 12:08

2 Answers 2

Reset to default
1

This seems a bit unorthodox, but this should do it for you if I understood the problem correctly:

SELECT d1.id, d1.value Cat1, d2.value Cat2, d3.value Cat3
FROM Docs d1 
    INNER JOIN Docs d2 ON d2.id = d1.id AND d2.rowNo = d1.rowNo AND d2.fieldNo = 3
    INNER JOIN Docs d3 ON d3.id = d2.id AND d3.rowNo = d1.rowNo AND d3.fieldNo = 5
        AND d1.fieldNo = 2

This solution of course expects values will exist for each column.

Revised answer...

If the third join and only the third join (Docs.fieldNo = 5) is optional, you can do something like this:

SELECT 
    d2.id, 
    d2.value Cat1, 
    d3.value Cat2, 
    d5.value Cat3
FROM
    (SELECT 2 fieldNo2, 3 fieldNo3, 5 fieldNo5) f
    INNER JOIN Docs d2 ON d2.fieldNo = f.fieldNo2 
    INNER JOIN Docs d3 ON d3.fieldNo = f.fieldNo3 and d3.rowNo = d2.rowNo and d3.id = d2.id
    LEFT JOIN Docs d5 ON d5.fieldNo = f.fieldNo5  and d5.rowNo = d2.rowNo and d5.id = d2.id

I've revised the rest of the query so that hopefully what it's doing is a little clearer.

Here are some answers on joins which you may find helpful: What is the difference between "INNER JOIN" and "OUTER JOIN"?

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I admit it's weird but the base table has this generic built and I might have to use at least cat1 and cat2 as IDs on different tables. It's not quite there yet. There is no XYZ in the output. See this fiddle
@TonyClifton I just realized I had a typo in one of the joins. I will take another look at your other requirement once I have a moment. This solution is not returning anything for XYZ because of the INNER JOINs.
@TonyClifton Is it possible to have a value for 3 if 2 does not exist. And likewise is it possible to have a value for 5 if 3 and 2 don't exist?
fieldNo 2 and 3 will always contain a value; 5 is only descriptive text that can have 0 or many rowNos. Actually values in fieldNo 2 will always start with 'E' and fieldNo 3 starts with 'D'
@TonyClifton I've revised the answer to hopefully better meet your needs.
|
0

I have tried this in an access database: replace myvalue with value (value is a reserved word in access) I called the table test so replace it with your tablename.

SELECT test.id, test.myValue AS cat1, test_1.[myValue] AS cat2, test_2.myValue AS cat3
FROM test, test AS test_1, test AS test_2
WHERE (((test.myValue) Like "cat1*") 
AND ((test_1.[myValue]) Like "cat2*") 
AND ((test_2.myValue) Like "cat3*"))
ORDER BY test.myValue, test_1.[myValue], test_2.myValue;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.