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.

Tony Clifton
  • 703
  • 3
  • 14
  • 27
  • 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. – Gordon Linoff Feb 13 '14 at 12:08

2 Answers2

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"?

Community
  • 1
  • 1
Manny
  • 967
  • 1
  • 6
  • 17
  • 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](http://sqlfiddle.com/#!6/8e6af/10) – Tony Clifton Feb 13 '14 at 13:23
  • @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 JOIN`s. – Manny Feb 13 '14 at 13:30
  • @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? – Manny Feb 13 '14 at 14:38
  • 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' – Tony Clifton Feb 14 '14 at 06:09
  • @TonyClifton I've revised the answer to hopefully better meet your needs. – Manny Feb 14 '14 at 15:29
  • thanks a lot @Manny - I think I'll be able to work with your solution. – Tony Clifton Feb 17 '14 at 09:52
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;
user2969649
  • 9
  • 2