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
taskhillAsked:
Who is Participating?
 
ralmadaConnect With a Mentor Commented:
If you're running SQL 2005 then:
I guess you have many different E16_DE values, so you will have to use dynamic SQL something like this:

Declare @strSQL varchar(max)        
Declare @cols varchar(2000)        
      
SELECT  @cols = STUFF(( SELECT DISTINCT         
                                '],[' + E16_DE        
                        From yourtable   
                        ORDER BY 1        
                        FOR XML PATH('')        
                      ), 1, 2, '') + ']'        
         
         
set @strSQL = 'select fk_E01_01, E16_03, ' + @cols +       
' from (       
        Select	fk_E01_01, 
				E16_DE, 
				E16_DE_Value, 
				max(E16_03) over (partition by as fk_E01_01) as E16_03 
		from yourtable) o       
pivot (max(E16_DE_Value) for E16_DE in (' + @cols + '))p'     
     
exec(@strSQL)

Open in new window

0
 
Michael VasilevskySolutions ArchitectCommented:
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
0
 
taskhillAuthor Commented:
This worked perfectly.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.