sql problem - 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

Old Forums

CLOSED - General SQL Server

sql problem

Author

Topic

xpandre
Posting Yak Master

212 Posts

Posted - 2002-02-28 : 05:25:10

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

2489 Posts

Posted - 2002-02-28 : 06:51:41

Check out this
http://sqlteam.com/item.asp?ItemID=256

Michael

xpandre
Posting Yak Master

212 Posts

Posted - 2002-02-28 : 06:56:03

thanx michael

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

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-02-28 : 07:16:51

Hi

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

Damian

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-02-28 : 07:18:50

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

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-02-28 : 07:20:28

Here is another old thread that should give you some ideas

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

Damian

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-28 : 07:30:57

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.

Nazim
A custom title

1408 Posts

Posted - 2002-02-28 : 07:45:00

nr, you cant run any DDL statements with Openrowset or linked servers. including Select into...

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

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-28 : 08:59:31

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.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-28 : 20:21:39

Anyone?

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

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-02-28 : 20:24:37

Bueller ?

Damian

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-28 : 20:25:25

Bueller?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-28 : 20:25:50

Anyone? Anyone?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-28 : 20:30:47

Ferris?

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

Nazim
A custom title

1408 Posts

Posted - 2002-03-02 : 01:08:45

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

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-02 : 09:33:58

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.

Nazim
A custom title

1408 Posts

Posted - 2002-03-02 : 23:20:38

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?

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

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-07 : 08:47:34

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.

ToddV
Posting Yak Master

218 Posts

Posted - 2002-03-07 : 09:14:20

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

Nazim
A custom title

1408 Posts

Posted - 2002-03-10 : 07:37:12

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

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-10 : 09:45:15

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.

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources