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

  • 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

  • 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
  • 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;

  • 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

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

    SqlIndia

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

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