Author |
Topic |
xpandre
Posting Yak Master
212 Posts |
Posted - 2002-02-28 : 05:25:10
|
i have a table with such data1 a11 a21a31 a42 a13 a14 a23 a42 a2now i want the result as1 a1,a21a3,a42 a1,a23 a1,a44 a2can i do this with a single select query?thanx a lotSam |
|
MichaelP
Jedi Yak
2489 Posts |
|
xpandre
Posting Yak Master
212 Posts |
Posted - 2002-02-28 : 06:56:03
|
thanx michaelbut 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
|
HiSort 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 |
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
|
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 = @idinsert #a select @id, @s end select * from #adrop table #a')But it seems it gets an error on #a doesn't exist.Evenselect * 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 executedselect * 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 ErrorCould 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 4select * 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 ErrorCould 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 ASET @SQL = SUBSTRING(@SQL,1,Len(@SQL)-1) + Char(13) + 'FROM #TEMP GROUP BY ID'PRINT @SQL --OR EXEC THISSELECT 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 tableQuery window - spid 7creates spid 10 for new connectioncreates #a on spid 10spid 10 logs out after a couople of minutesin single window create #a, #b, #c, #cSingle run with go between each statementquery spid = 8#a spid = 9#b spid = 9#c spid = 9#c spid = 9 -- errorAs above but each statement run separatelyquery spid = 8#a spid = 9#b spid = 9#c spid = 9#c spid = 10 ?????Query window - spid 8creates spid 9 for new connectioncreates #a on spid 9Query window - spid 11creates #b on spid 10Query window - spid 8creates spid 12 for new connectioncreates #a on spid 12Query window - spid 8creates #a on spid 9 -- failure==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
Next Page
|