What's new
Carbonite

Welcome to Carbonite! Register a free account today to become a member! Once signed in, you'll be able to participate on this site by adding your own topics and posts, as well as connect with other members through your own private inbox!

MS SQL View

z3llin

Well-Known member
Rating - 100%
9   0   0
Joined
Apr 22, 2010
Messages
287
Reaction score
97
Points
1,385
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.
 

akafaar

Junior Member
Rating - 100%
6   0   0
Joined
Oct 15, 2018
Messages
55
Reaction score
15
Points
535
Age
31
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

z3llin

Well-Known member
Rating - 100%
9   0   0
Joined
Apr 22, 2010
Messages
287
Reaction score
97
Points
1,385
Location
Northern Suburbs
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!
 

Eddie

Junior Member
Rating - 100%
9   0   0
Joined
Oct 17, 2016
Messages
82
Reaction score
97
Points
645
Location
Midrand
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.
 

z3llin

Well-Known member
Rating - 100%
9   0   0
Joined
Apr 22, 2010
Messages
287
Reaction score
97
Points
1,385
Location
Northern Suburbs
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.
 

Top Donors

$320.00
$235.00
$210.00
$185.00
$182.00
Top