SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Trim
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 01/31/2010 :  21:09:54  Show Profile  Reply with Quote
Table Name = Med

Med
med1, med3, med5

I want to create a view that would be viewed like this:

Med
med1
med3
med5

Any help is appreciated. Thanks

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 01/31/2010 :  21:12:10  Show Profile  Reply with Quote
use one of the following
CSVTable
fnParseList


KH
Time is always against us

Go to Top of Page

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 01/31/2010 :  21:32:28  Show Profile  Reply with Quote
is it possible without creating a stored procedure?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 01/31/2010 :  22:01:09  Show Profile  Reply with Quote
those are function not stored procedure


KH
Time is always against us

Go to Top of Page

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 01/31/2010 :  22:02:18  Show Profile  Reply with Quote
i really don't understand how.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 01/31/2010 :  22:10:39  Show Profile  Reply with Quote

begin tran
-- Create a table for testing and for illustration of the function
create table Med
(
	csvmed	varchar(20)
)

-- Insert some sample data into the testing table
insert into Med (csvmed)
select	'med1, med2, med3'	union all
select	'med4, med5, med6'

-- the Query. Which you can convert into a View easily 
select	stringval as med
from	Med m
	cross apply dbo.CSVTable(csvmed)

rollback



KH
Time is always against us

Go to Top of Page

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 01/31/2010 :  22:14:37  Show Profile  Reply with Quote
am getting this error:
Line 3: Incorrect syntax near 'apply'.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 01/31/2010 :  22:16:28  Show Profile  Reply with Quote
what is the version of your SQL Server ?


KH
Time is always against us

Go to Top of Page

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 01/31/2010 :  22:16:58  Show Profile  Reply with Quote
2000 sir
Go to Top of Page

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 01/31/2010 :  22:25:14  Show Profile  Reply with Quote
I got the error when I tried running this script:

select stringval as med
from Med m
cross apply dbo.CSVTable(csvmed)
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 01/31/2010 :  22:28:57  Show Profile  Reply with Quote
CROSS APPLY is only available for SQL 2005/2008


KH
Time is always against us

Go to Top of Page

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 01/31/2010 :  22:30:17  Show Profile  Reply with Quote
any option for 2000? :(
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 01/31/2010 :  22:30:21  Show Profile  Reply with Quote
for 2000, it will not be easy. What are you trying to achieve here ?


KH
Time is always against us

Go to Top of Page

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 01/31/2010 :  22:32:24  Show Profile  Reply with Quote
i would like to have results that would like like this:

MED
med1
med2
med3
med4
med5
med6

Go to Top of Page

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 01/31/2010 :  22:47:52  Show Profile  Reply with Quote
Hi I tried it in 2005 but I got this message:

Invalid object name 'dbo.CSVTable'.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 01/31/2010 :  23:38:03  Show Profile  Reply with Quote
quote:
Originally posted by cutiebo2t

Hi I tried it in 2005 but I got this message:

Invalid object name 'dbo.CSVTable'.



see my 1st post. Get it from the link


KH
Time is always against us

Go to Top of Page

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 02/01/2010 :  01:10:41  Show Profile  Reply with Quote
I got it. What if I want it in a horizontal view. Like this:

med1 med2 med3 med4 med5 med6
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 02/01/2010 :  01:16:20  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by cutiebo2t

I got it. What if I want it in a horizontal view. Like this:

med1 med2 med3 med4 med5 med6


Where do you want to show the data?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 02/01/2010 :  01:18:39  Show Profile  Reply with Quote
in a view in a columnar format. thanks
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 02/01/2010 :  01:36:12  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by cutiebo2t

in a view in a columnar format. thanks


Are you going to display them in a front end application?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 02/01/2010 :  01:56:15  Show Profile  Reply with Quote
no backend
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000