July 17, 2007 at 10:24 am
Hello
i have a table with the following structure
cust.id, cust.contacttype (eg:SMS, email), cust.contactemail (eg:1234,x@x.x)
i.e many ligns for one customer id
I want to create a view with the following structure:
cust.id , cust.SMS , cust.email
i.e one lign per customer, and as much columns as contact types records
What would my view query be?
tks!!!
July 17, 2007 at 11:18 am
There isn't a good way to do this. A view needs a definition, so you'd need to determine how many contact records there are and build them into the view. You could return blanks if there wasn't a 3rd or 4th record for someone, but you can't dynamically change the size of the view.
I think you'd be better off with a query and properly displaying the on the client, dealing with the repeating data of the customer id.
July 17, 2007 at 3:51 pm
and what if i know how many contacts type i have? actually right no i only have 2...
July 18, 2007 at 12:28 am
Could you post some sample data and your desired output please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2007 at 2:11 am
Create view LProva as
(
Select distinct c.[cust.id], a.sms, b.email from
(Select [cust.id], [cust.contactemail] sms from Prova where [cust.contacttype] = 'sms' ) a,
(Select [cust.id], [cust.contactemail] email from Prova where [cust.contacttype] = 'email' ) b,
Prova c
where c.[cust.id] *= a.[cust.id] and c.[cust.id] *= b.[cust.id]
)
Table = Prova, View = LProva
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply