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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 sql problem
 Forum Locked
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

xpandre
Posting Yak Master

212 Posts

Posted - 02/28/2002 :  05:25:10  Show Profile
i have a table with such data

1 a1
1 a21a3
1 a4
2 a1
3 a1
4 a2
3 a4
2 a2


now i want the result as
1 a1,a21a3,a4
2 a1,a2
3 a1,a4
4 a2

can i do this with a single select query?

thanx a lot
Sam

MichaelP
Jedi Yak

USA
2489 Posts

Posted - 02/28/2002 :  06:51:41  Show Profile  Visit MichaelP's Homepage
Check out this
http://sqlteam.com/item.asp?ItemID=256

Michael

Go to Top of Page

xpandre
Posting Yak Master

212 Posts

Posted - 02/28/2002 :  06:56:03  Show Profile
thanx michael

but i was just wonderin if i could do it using a single select query rather than using cursors..
thanx
Sam

Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 02/28/2002 :  07:16:51  Show Profile  Visit Merkin's Homepage
Hi

Sort of. You can do it with a loop and a temp table. Which is similar, but not quite a cursor.

Damian
Go to Top of Page

MichaelP
Jedi Yak

USA
2489 Posts

Posted - 02/28/2002 :  07:18:50  Show Profile  Visit MichaelP's Homepage
Take a look at this. This was in the comments of the first article I posted.

http://sqlteam.com/forums/topic.asp?TOPIC_ID=5941

Michael

Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 02/28/2002 :  07:20:28  Show Profile  Visit Merkin's Homepage
Here is another old thread that should give you some ideas

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=7075

Damian
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 02/28/2002 :  07:30:57  Show Profile  Visit nr's Homepage
Thought it was possible with this (don't recommend it)

select * from openrowset('MSDASQL',
'DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=pppppp',
'set nocount on
declare @id int, @s varchar(100)
create table #a(id int, s varchar(1000))
set @id = 0
while @id < (select max(id) from test..z)
begin
select @id = min(id) from test..z where id > @id
select @s = coalesce(@s + '','','''') + val from test..z where id = @id
insert #a select @id, @s
end
select * from #a
drop table #a
'
)

But it seems it gets an error on #a doesn't exist.
Even

select * from openrowset('MSDASQL',
'DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=pppppp',
'set nocount on create table #a (id int) select * from #a)'

gets the same error.

Also seems that it holds and re-uses the connection as if you create the table in a call it will exist for the next (but you have to drop it first or get an already exists error).
Even putting the code inside an exec doesn't seem to help - that's a bit surprising.
Seems the sytax check looks for the table even inside dynamic sql!

select * from openrowset('MSDASQL',
'DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=pppppp',
'set nocount on exec (''create table #a (id int) select * from #a''))'

Interested if anyone wants to take this further.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Nazim
A custom title

United Arab Emirates
1408 Posts

Posted - 02/28/2002 :  07:45:00  Show Profile
nr, you cant run any DDL statements with Openrowset or linked servers. including Select into...



--------------------------------------------------------------
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 02/28/2002 :  08:59:31  Show Profile  Visit nr's Homepage
Yes you can - it creates the table, just checks the existence of the table for the select first.

it is happy with this when executed separately in the same query window.

select * from openrowset('MSDASQL',
'DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=pppppp',
'set nocount on create table #a (id int) select 1')

select * from openrowset('MSDASQL',
'DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=pppppp',
'set nocount on insert #a select 1 select id from #a drop table #a')



==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 02/28/2002 :  20:21:39  Show Profile  Visit nr's Homepage
Anyone?

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 02/28/2002 :  20:24:37  Show Profile  Visit Merkin's Homepage
Bueller ?

Damian
Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 02/28/2002 :  20:25:25  Show Profile  Visit robvolk's Homepage
Bueller?

Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 02/28/2002 :  20:25:50  Show Profile  Visit robvolk's Homepage
Anyone? Anyone?

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 02/28/2002 :  20:30:47  Show Profile  Visit nr's Homepage
Ferris?


==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Nazim
A custom title

United Arab Emirates
1408 Posts

Posted - 03/02/2002 :  01:08:45  Show Profile
Koi hai??


i have some MS documents which says you cant issue DDL statements with Linked Servers.
quote:

Yes you can - it creates the table, just checks the existence of the table for the select first.




i tried this. though it does gets executed am not able to find #a anywhere . i tried creating a permanent table too though this returns 1 but the table is created nowhere. Am Confused. Is the table created or not ?. if it is created where is it?

This is what i executed


select * from openrowset('SQLOLEDB','server=east01;UID=sa;PWD=pppp',
'set nocount on create table test..kk (id int) select 1')




quote:

select * from openrowset('MSDASQL',
'DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=pppppp',
'set nocount on create table #a (id int) select 1')




This gives me a Error

Could not process object 'set nocount on insert #a select 1 select id from #a drop table #a'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.



--------------------------------------------------------------


Edited by - Nazim on 03/02/2002 01:11:27
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 03/02/2002 :  09:33:58  Show Profile  Visit nr's Homepage
Try executing these statements separately from the same query window.
(On v7 - haven't tried 2000)

select * from openrowset('MSDASQL',
'DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=pppppp',
'set nocount on create table #a (id int) select 1')

select * from openrowset('MSDASQL',
'DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=pppppp',
'set nocount on insert #a select 1 select id from #a drop table #a')



==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Nazim
A custom title

United Arab Emirates
1408 Posts

Posted - 03/02/2002 :  23:20:38  Show Profile

In my Previous posts i had tested it using Sql 2000.


With 7.0 Service pack 4

select * from openrowset('MSDASQL',
'DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=pppppp',
'set nocount on create table #a (id int) select 1')

Am not sure how does this works.

when i execute it repeatedly . i could find in tempdb..sysobjects '#a' atleast 3 instances of #a (it doesnt allows more then 3).


quote:

select * from openrowset('MSDASQL',
'DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=pppppp',
'set nocount on insert #a select 1 select id from #a drop table #a')


Gives me a Error

Could not process object 'set nocount on insert #b select 1 select id from #a drop table #a'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.


Another thing .

i can create only temp tables. when i tried creating a permanent table it gives a error.


Any thoughts?



--------------------------------------------------------------
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 03/07/2002 :  08:47:34  Show Profile  Visit nr's Homepage
An openrowset has to return a resultset.
It looks like the statement is first parsed to get the format of that resultset - which is why the select 1 is needed in the first statement. The no columns message comes if it can't find the resultset.
Probably also why it discovers that #a does not exist if both statements are executed together.

Bit surprised the second statement gave you that error. I notice that the error message has 'insert #b'.

I'll try it with various versions when I get time.


==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page

ToddV
Posting Yak Master

USA
218 Posts

Posted - 03/07/2002 :  09:14:20  Show Profile
Here is a possible solution which may or may not be appropriate for your situation. The first block Produces the code you need to denorm you table. YOu can either grab it out and use it hard coded or exec it dynamically. The problem comes if there are so many possible values for Col1 that the code grows larger than 8000 characters.

Create table #Temp (
Id INT,
Col1 Varchar(10))

Insert #Temp VALUES(1,'a1')
Insert #Temp VALUES(1 ,'a21a3')
Insert #Temp VALUES(1 ,'a4')
Insert #Temp VALUES(2 ,'a1')
Insert #Temp VALUES(3 ,'a1')
Insert #Temp VALUES(4 ,'a2')
Insert #Temp VALUES(3 ,'a4')
Insert #Temp VALUES(2 ,'a2')

DECLARE @SQL Varchar(8000)
SET @SQL = 'SELECT ID,'

SELECT @SQL = @SQL + CHAR(13) + CHAR(9) + 'MAX(CASE WHEN Col1 = ' + QUOTENAME(Col1,'''') + ' THEN '','' + Col1 ELSE '''' END)+'
FROM (SELECT Distinct Col1 FROM #Temp) AS A

SET @SQL = SUBSTRING(@SQL,1,Len(@SQL)-1) + Char(13) + 'FROM #TEMP GROUP BY ID'

PRINT @SQL --OR EXEC THIS

SELECT ID,
MAX(CASE WHEN Col1 = 'a1' THEN ',' + Col1 ELSE '' END)+
MAX(CASE WHEN Col1 = 'a2' THEN ',' + Col1 ELSE '' END)+
MAX(CASE WHEN Col1 = 'a21a3' THEN ',' + Col1 ELSE '' END)+
MAX(CASE WHEN Col1 = 'a4' THEN ',' + Col1 ELSE '' END)
FROM #TEMP GROUP BY ID

Go to Top of Page

Nazim
A custom title

United Arab Emirates
1408 Posts

Posted - 03/10/2002 :  07:37:12  Show Profile
I Cant understand why it only allows 3 instances of #a to be created , but not more than that. i have tested this repeatedly with different names for temp tables using Sql Server 7.0 Service Pack 4. and why doesnt it allows a permanent table to be created.

quote:

An openrowset has to return a resultset.
It looks like the statement is first parsed to get the format of that resultset - which is why the select 1 is needed in the first statement. The no columns message comes if it can't find the resultset.
Probably also why it discovers that #a does not exist if both statements are executed together.




--------------------------------------------------------------


Edited by - Nazim on 03/10/2002 07:38:31
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 03/10/2002 :  09:45:15  Show Profile  Visit nr's Homepage
Bounced the server between each test.
It appears that if you create different temp tables in each statement then it will use the same spid (similar to connection pooling). If you try to create an existing temp table then it will create a new spid. If you try this again then it will use the first spid and get an error. I suspect it is is just trying a different spid but doesn't check again to see if the table exists.


Single openrowset to create temp table

Query window - spid 7
creates spid 10 for new connection
creates #a on spid 10
spid 10 logs out after a couople of minutes

in single window create #a, #b, #c, #c
Single run with go between each statement
query spid = 8
#a spid = 9
#b spid = 9
#c spid = 9
#c spid = 9 -- error

As above but each statement run separately
query spid = 8
#a spid = 9
#b spid = 9
#c spid = 9
#c spid = 10 ?????

Query window - spid 8
creates spid 9 for new connection
creates #a on spid 9
Query window - spid 11
creates #b on spid 10
Query window - spid 8
creates spid 12 for new connection
creates #a on spid 12
Query window - spid 8
creates #a on spid 9 -- failure

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000