T-SQL HELP

Question

T-SQL HELP

New Starter

SSC Veteran

Points: 212
More actions
January 19, 2006 at 5:42 am

#93175

Hi,
I have a table like the one below. It contains 110 columns and records for about 200 users. What I need to do is find out how many users have any entry in any column
like EXT,
as there are 60 columns which start with Ext, it should look in each column to see if it has a entry like cb_ (ie '%cb_%' ).
table = TEST
Username
Col01
Col
02

Ext01...

Ext02

Ext60
Col
110
User1

test_
cb_
hjiu
mb_th
User2

two
cb_tes
r
cb_test
cb_
User200

cb_
one
th_yt
gh_
cb_

The final result should then show each username and also the access they have to which Ext as long as it has "cb_" in it
For example in the table above user1 matches test_cb only
user2 matches twocb_tes and
rcb_test and cb_
user200 matches cb_one and gh_cb_
The query below shows how to find the information for just one column, but how do you find the result for all 60 Ext columns and all users
select distinct (username),Ext03
from TEST
where EXT03 like '%cb_%'
the above query example will return
username
User3 cb_one
User3 gh_cb_
How can I write a T-SQL statement that will do this, I have managed to do this by creating multiple union all statements (60) and creating a view (to group on), but I want to know if this can be achieved through using a simple T-SQL statement. In order to look at the columns I looked in the syscolumns table to match any columns like Ext
The final result should only show one occurance of each entry
Thanks
Kindest Regards,
Rookie_DBA

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 1

I am not sure precisely what you want. For the table above (with columns Col02, Ext01, Ext02, Ext60 and Col 110), could you describe (e.g. with a similar table display) the output you want?

Also note that "_" will match any single character in a like statement. Maybe you want '%cb[_]%' instead...

New Starter SSC Veteran Points: 212 More actions · Answer 2

Hi, Thanks for the quick reply. I just noticed that on the original post the table has shitfted the data. it should look like the table below..im only interested in the data which is under any column like Ext (I knwo that there are 60 altogether ranging from Ext01 to Ext60)

table = TEST

Username Col01	Col 02	Ext01...	Ext02	Ext60	Col 110
User1		test_cb_	hjiu	mb_th
User2		twocb_tes	rcb_test	cb_
User200		cb_one	th_yt	gh_cb_

Kindest Regards,

Rookie_DBA

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 3

OK, but I am still puzzled as to what the result of the query should be... How many columns are there in the result and what data should be in each column?

David Burrows SSC Guru Points: 65142 More actions · Answer 4

DECLARE @max-2 int, @id int, @sql nvarchar(1000)

CREATE TABLE #temp1([ID] int IDENTITY(1,1), COLUMN_NAME sysname)

INSERT INTO #temp1(COLUMN_NAME) SELECT COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'TEST' AND COLUMN_NAME LIKE 'Ext%'

SET @max-2 = SCOPE_IDENTITY()

CREATE TABLE #temp2(Username varchar(20), Value varchar(20))

SET @id = 1

WHILE @id <= @max-2

BEGIN

SELECT @sql = 'SELECT Username,'+COLUMN_NAME+

' FROM TEST WHERE '+COLUMN_NAME+' LIKE ''%cb[_]%'''

FROM #temp1 WHERE [ID] = @id

INSERT INTO #temp2 (Username,Value) EXEC(@sql)

SET @id = @id + 1

END

SELECT DISTINCT Username,Value FROM #temp2

DROP TABLE #temp1

DROP TABLE #temp2

Far away is close at hand in the images of elsewhere.
Anon.

New Starter SSC Veteran Points: 212 More actions · Answer 5

Hi David,

That did the trick perfectly...

Could you kindly recommend any good books that will allow me to advance my T-SQL skills please?

thanks for your help again

Kindest Regards,

Rookie_DBA

New Starter SSC Veteran Points: 212 More actions · Answer 6

Hi Jesper,

Thanks for your help...its sorted now...apolgoies for my lack of clarity. Im new to this and will aim to be clearer in the future.

many thanks

Kindest Regards,

Rookie_DBA

David Burrows SSC Guru Points: 65142 More actions · Answer 7

Not me personally

but if you search this forum there will be plenty of advice

two threads to get you going

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=5834

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=17678

Far away is close at hand in the images of elsewhere.
Anon.

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 8

No problem, David is great in guessing what people want....

For me personally, the best way to learn about T-SQL is to follow the discussions on this site. Try to solve some of the problems yourself, or post your own problems. I think you learn a lot from other people's posts, and everyone is nice and patient...

David Burrows SSC Guru Points: 65142 More actions · Answer 9

David is great in guessing what people want....

Thanks Jesper, I owe it to my Ouija board

For me personally, the best way to learn about T-SQL is to follow the discussions on this site. Try to solve some of the problems yourself, or post your own problems. I think you learn a lot from other people's posts, and everyone is nice and patient...

Couldn't agree more

Far away is close at hand in the images of elsewhere.
Anon.

New Starter SSC Veteran Points: 212 More actions · Answer 10

Thank you very much again ....

no doubt I will be in touch soon...

Kindest Regards,

Rookie_DBA