Can we?

Question

Can we?

Dave-976292

Newbie

Points: 1
More actions
December 29, 2008 at 7:46 am

#125308

How?

Viewing 13 posts - 1 through 12 (of 12 total)

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

Sure. The technique below works fast for concatenating values in any column. The cte isn't required, it just makes the final query a little easier to read and understand.

;with cte1(col,tbl) as

(select c.name, object_name(c.object_id)

from sys.columns c

join sys.objects o on c.object_id = o.object_id

where o.type = 'U'

)

SELECT tbl, columnList = substring((SELECT ( ', ' + col)

FROM cte1 c1

WHERE c1.tbl = cgroup.tbl

ORDER BY tbl, col

FOR XML PATH( '' )), 3, 1000 )

FROM cte1 cgroup

GROUP BY tbl

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=CreateACommaDelimitedList&referringTitle=Home

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

Dave, sorry I was editing my post when you looked a minute ago. The cut and paste of the code looked really squirrelly because of tabs settings. Please post the entirety of your original question back for others to see.

Let me know if you have any questions.

Bob

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Bob - you must be a SERIOUS mindreader. Coming up with the right code that answer the question (consisting only of Can we?......How>) - that's truly impressive.

Say - do you see 5 or six numbers in your head right now? I need some choices for the Pick 6 tonight:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

Matt: It's a gift. I am but a tool of a Higher Power 😉

If you see my post above, we just had a touch of asynchronous communications. He originally posted a full question complete with sample code which I responded to, but the query I pasted in looked janky and I was editing my post when he came back to look. Instead of adding a reply, he edited his original post. I was confused for a moment too.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

.... and if I could pick numbers, I would be retired and skiing in Park City or Alta right now, so you would have never seen me on this forum. 😀

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

Here's the op's original question:

Here is a code that is returning a list of all the user tables with their fields list.
My question is can we re-write this code without cursor or looping?
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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 7

And here's the information_schema 'optional non-cte version'

select t.table_name,

( select stuff(

( select ',' + c.column_name

from information_schema.columns c

where c.table_name = t.table_name

order by c.ordinal_position

for xml path('')

)

, 1, 1, '')

) as fieldlist

from information_schema.tables t

where t.table_type = 'base table'

/Kenneth

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

Thanks, Kenneth. Maybe it's holiday silliness, but I kind of like the idea of a post topic being simply "Can we?"

Yes, we can.

Bob (the Builder)

P.S. I like your non-CTE version using information_schema.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills