Help in query

Question

Help in query

Ak-763166

Right there with Babe

Points: 772
More actions
October 7, 2008 at 2:01 am

#199315

Below is a query which works fine. it dispays the table name,index name and associated column name.
SELECT obj.name tableName,
ind.name AS IndexName,
col.name AS ComponentName
FROM dbo.syscolumns Col
RIGHT OUTER JOIN dbo.sysindexkeys as indkey
INNER JOIN dbo.sysobjects obj ON indkey.id = obj.id
INNER JOIN dbo.sysindexes ind ON indkey.indid = ind.indid AND obj.id = ind.id
ON
col.id = obj.id AND col.colid = indkey.colid
WHERE obj.name='my_table' and (ind.indid BETWEEN 1 AND 254) AND (ind.Status & 64)=0
However it is displayed if there is an index name in 2 columns, there is two rows which is outputted as below:
TableName Indexname ComponentName
table1 IX_1 col1
table1 IX_1 col2
What i want is to output the records as follows, where there is a single record for both column ( I dont want to use cursor here):
TableName Indexname ComponentName
table1 IX_1 col1,col2
Please post if you have any ideas.
Amit

Viewing 3 posts - 1 through 2 (of 2 total)

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

Jeff Moden SSC Guru Points: 1003925 More actions · Answer 1

This type of concatenation is usually a bad idea especially if your intent is to store the result somewhere in the database as permanent data (not that you are, but had to say it "out loud"). 🙂

Please take a look at the following article. It's got a pretty good "how to" in it as well as some performance pitfalls to avoid...

http://www.sqlservercentral.com/articles/Test+Data/61572/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 2

Also refer

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

Madhivanan

Failing to plan is Planning to fail