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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
ralmadaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
taskhillAuthor Commented:
This worked perfectly.  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.