Creating 1 Select Query without Cursor, Function, temp object or Loop

Question

Creating 1 Select Query without Cursor, Function, temp object or Loop

SQLIndia

Ten Centuries

Points: 1073
More actions
December 26, 2012 at 2:41 pm

#393842

Hi All
I have a query that need to Rewrite it without cursor, function or loop to produce same result and should be included in ONE SELECT (no temporary object involved).
Code.............................................
declare @ColName sysname,
@TableName sysname,
@FieldList varchar(MAX),
@LastTableName sysname
select @LastTableName = '',
@FieldList = ''
declare @tb table (TableName sysname null, FieldList varchar(MAX) null)
declare curs cursor for
select c.name, object_name(c.object_id)
from sys.columns c INNER JOIN sys.objects o on c.object_id = o.object_id
where o.type = 'U'
order by o.object_id
open curs
fetch curs into @ColName, @TableName
set @LastTableName = @TableName
while @@FETCH_STATUS = 0
BEGIN
if @LastTableName <> @TableName
BEGIN
insert into @tb values (@LastTableName,@FieldList)
set @FieldList = ''
set @LastTableName = @TableName
END
set @FieldList = case when @FieldList = '' then @ColName else + @FieldList + ',' + @ColName end
fetch curs into @ColName, @TableName
END
deallocate curs
insert into @tb values (@LastTableName,@FieldList)
select * from @tb
.............................................Code END
Pls help
Thanks in Advance
SqlIndia

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

sqlindia (12/26/2012)
I have a query that need to Rewrite it without cursor, function or loop to produce same result and should be included in ONE SELECT (no temporary object involved).

Why those restrictions? Sounds like rules for a test or competition...

Look up the FOR XML method of string concatenating.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Something like this:

WITH CTE AS

(

SELECT

name as TableName,

object_id

FROM sys.tables WHERE schema_id = schema_id('dbo')

)

SELECT TableName,

ColumnList = STUFF((

SELECT ',' + c.name

FROM sys.columns c

WHERE c.object_id = CTE.object_id

ORDER BY c.column_id

FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,'')

FROM CTE

ORDER BY TableName;

SQLIndia Ten Centuries Points: 1073 More actions · Answer 3

you are right Gila...happened at work and we testing the situation as this raised by someone in group at work.

and I was not able figure after tried. Thought to ask in forum for help.

thanks..

SqlIndia

SQLIndia Ten Centuries Points: 1073 More actions · Answer 4

Thanks Lynn...I'll check and share the results..

SqlIndia