asked on

Create view in SQL

Greetings experts.

I have a delima on my hands.

I have a DB table with the following structure:
pk_E16_00_0 (PK, int, not null)
fk_E01_01 (FK, int, not null)
E16_03 (datetime, null)
E16_DE (char(6), not null)
E16_DE_Value (int, not null)

This provides output like this

pk_E16_00_0 | fk_E01_01 | E16_03 | E16_DE | E16_DE_Value
---------------------------------------------------------------------------------------------
517 | 74 | 8/22/2007 2:56:00 AM | E16_04 | 3420
518 | 74 | 8/22/2007 2:56:00 AM | E16_05 | 3851
519 | 74 | 8/22/2007 2:57:00 AM | E16_07 | 8182
520 | 74 | 8/22/2007 2:57:00 AM | E16_10 | 7982
521 | 74 | 8/22/2007 2:58:00 AM | E16_13 | 4982

The problem I am facing is the values stored in E16_DE and E16_DE_Value would be better served in their own fileds like below.

fk_E01_01 | E16_03 | E16_04 | E16_05 | E16_06 | E16_07 |...| E16_24
74 |8/22/2007 2:58:00 AM | 3420 | 2250 | 8741 | 4512 |...| 8569

Is there a way to create a view that splits the table into something similar as the above. The current table is necessary for other systems and cannot be changed.

I only have one semseter of SQL under my belt and have a good grasp of the basics. However, I get scared when I see stuff more advanced than SELECT, FROM, WHERE: So be gentle

Thanks,

Task

Michael Vasilevsky

You'll want to use a crosstab query. Something like:

TRANSFORM E16_DE_Value
SELECT * FROM MyTable
GROUP BY fk_E01_01, E16_03
PIVOT E16_DE

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

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

Start Free Trial

taskhill

ASKER

This worked perfectly.