What's new
Carbonite

South Africa's Top Online Tech Classifieds!
Register a free account today to become a member! (No Under 18's)
Home of C.U.D.

MS SQL View

z3llin

Epic Member
Rating - 100%
14   0   0
Joined
Apr 22, 2010
Messages
364
Reaction score
85
Points
3,815
Location
Northern Suburbs
So SQL is not exactly my ball park & the Tables I have to work with cant be modified.

TableA Document,Customer,Store DOC01,CUST01,STORE2 DOC02,CUST01,STORE2 DOC03,CUST02,STORE1 DOC04,CUST02,STORE1 TableB Customer,Store,DefiningField,Value CUST01,STORE2,"Is Real",TRUE CUST01,STORE2,"STAFF",7 CUST02,STORE1,"Is Real",FALSE CUST02,STORE1,"STAFF",3 TableC Document,RandomInfo,Blah1,Blah2,Blah3 DOC01,RandomInfo1,blah,blah,blah DOC02,RandomInfo2,blah,blah,blah DOC03,RandomInfo3,blah,blah,blah DOC04,RandomInfo4,blah,blah,blah

The following provides ViewD:
Select TableA.Customer,TableA.Store,TableA.Store,TableC.RandomInfo,TableC.Blah1, From TableA LEFT OUTER JOIN TableC ON TableA.Document = TableC.Document ViewD Customer,Store,Document,RandomInfo CUST01,STORE2,DOC01,RandomInfo1 CUST01,STORE2,DOC02,RandomInfo2 CUST01,STORE1,DOC03,RandomInfo3 CUST01,STORE1,DOC04,RandomInfo4

Now I need to add information from TableB in to get ViewE.
ViewE Customer,Store,Document,RandomInfo,"Is Real","STAFF" CUST01,STORE2,DOC01,RandomInfo1,Blah,TRUE,7 CUST01,STORE2,DOC02,RandomInfo2,Blah,TRUE,7 CUST01,STORE1,DOC03,RandomInfo3,Blah,FALSE,3 CUST01,STORE1,DOC04,RandomInfo4,Blah,FALSE,3

I can join on the customer and store without an issue, but how do I break out DefiningField values into different columns?
If I use a WHERE DefiningField = 'Is Real' I can return one of the columns, but not both.
 
Hey Bud.

You can accomplish the above with some case statements or pivot tables.

I've put together an example using temp tables, pivoting the DefiningField into columns, so should put you into the right direction.

Lemme know if you want me to explain further in a PM.

Cheers,
Agmad
 

Attachments

  • pivot_query_mssql.txt
    1.6 KB · Views: 21
Hey Bud.

You can accomplish the above with some case statements or pivot tables.

I've put together an example using temp tables, pivoting the DefiningField into columns, so should put you into the right direction.

Lemme know if you want me to explain further in a PM.

Cheers,
Agmad
I didnt know about Pivoting in SQL,I'll look into it and give you a shout if I get stuck.

Thank you!
 
Just note that if you are going to run queries on a view containing pivots, you'll soon run into performance issues as the query plan will no longer use (your intended) indexes; Same as with functions.
I'd rather just write out all the outer joins.
If the you never intend to do anything other than a straight forward select from your new view, you can pivot all you want.
 
Just note that if you are going to run queries on a view containing pivots, you'll soon run into performance issues as the query plan will no longer use (your intended) indexes; Same as with functions.
I'd rather just write out all the outer joins.
If the you never intend to do anything other than a straight forward select from your new view, you can pivot all you want.
Thanks, performance is a concern, but the intention is to only ever select from the view.
 

Users who are viewing this thread

Back
Top Bottom