create a view - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

create a view

Author

Topic

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-11-06 : 13:57:55

thanks

AASC
Starting Member

24 Posts

Posted - 2014-11-06 : 14:16:19

I supposed that only two email lines are per customer

---create table to test query
create table customer
( customer varchar(200), email varchar(20),line int)

---insert Dumpy data
insert into customer
select 'cust001','abc@.com', 1
union
select 'cust001','adc@.com',2
union
select 'cust002','fc@.com',1
union
select 'cust003','gbc@.com',1
union
select 'cust003','hbc@.com',2
GO

--- create view for customer email accounts
create view V_CustomerEmaillines
AS

select customer, max(case EmailLine when 1 then email else '' end) emailine1 , max(case EmailLine when 2 then email else '' end) emailine2
from(
select row_number()over(partition by customer order by line asc) EmailLine,*
from customer
)cusemail
group by customer

GO

---- verify view
select * from V_CustomerEmaillines

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-11-06 : 14:48:37

thanks it works fine. I am able to edit this view , open but only when I right click on the view and select design this crushes the server.

AASC
Starting Member

24 Posts

Posted - 2014-11-07 : 10:31:33

you can get script of View using tsql command

Use yourDatabaseName
GO
sp_helptext 'Your view name'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources