Find a value in multiple columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Find a value in multiple columns

Author

Topic

JSanderson
Starting Member

3 Posts

Posted - 2003-10-21 : 10:31:07

Hi, all (first post to SQLteam forums... excellent site!).

I need to do the following in a Stored Procedure...

For all columns in a table where SUBSTRING(COLUMN_NAME,1,1) = 'r' (in my table, all columns that begin with 'r' are INT), count the number of times a particular value (let's say 21) appears in those columns, and put that count in a new column in a result set (i.e there's one 'count' per row).

I'm looking at some of the articles about creating/parsing 'arrays' of values, but not sure I'm heading in the right direction.

Any help greatly appreciated.

Jim.

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2003-10-21 : 11:13:29

Hi Jim

Give this code a try. I created a test table and populated it with 2 instead of 21, but you can modify your search criteria using the @search_int variable.

----
-- Variables
DECLARE @column VARCHAR(20), @cmd VARCHAR(1000), @search_int INT
SET @search_int = 2 -- set the value to whatever you like here

-- Create a results table
CREATE TABLE #results (column_name VARCHAR(20), search_count INT)

-- Get all 'r' INTEGER columns into a cursor
DECLARE rcolumns CURSOR FORWARD_ONLY FOR
SELECT column_name FROM INFORMATION_SCHEMA.columns
WHERE table_name = 'sqlteamtest'
AND column_name LIKE 'r%'
AND data_type = 'INT'

-- Loop through the cursor
OPEN rcolumns
FETCH NEXT FROM rcolumns INTO @column
WHILE @@FETCH_STATUS =0
BEGIN
-- Get the count into a string command and execute it
SET @cmd = 'INSERT INTO #results SELECT ''' + @column + ''', COUNT(*) FROM sqlteamtest WHERE ' + @column + ' = ' + CONVERT(VARCHAR, @search_int)
EXECUTE (@cmd)

-- Next column
FETCH NEXT FROM rcolumns INTO @column
END

-- Results
SELECT * FROM #results

-- Cleanup
CLOSE rcolumns
DEALLOCATE rcolumns
DROP TABLE #results

----

Raymond

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-10-21 : 12:56:25

Raymond's solution works but I don't think this exercise requires a cursor to be honest

Something like :


CREATE TABLE TESTING (B1 CHAR(2), R1 SMALLINT, R2 SMALLINT)

INSERT INTO TESTING (B1, R1, R2)
SELECT	'AA', 1, 2 UNION ALL
SELECT	'BB', 21, 21 UNION ALL
SELECT	'BC', 21, 21 UNION ALL
SELECT	'BD', 21, 21 UNION ALL
SELECT	'BE', 21, 21 UNION ALL
SELECT	'BF', 21, 21 UNION ALL
SELECT	'BG', 21, 21

DECLARE @TABLENAME VARCHAR(20)
DECLARE @SQL_STETMENT VARCHAR(1000) 

SET @TABLENAME = 'TESTING'

SET @SQL_STETMENT = '' 
SELECT @SQL_STETMENT = @SQL_STETMENT + 'SELECT ' + NAME + ' , COUNT
(*) FROM ' + @TABLENAME + ' WHERE ' + NAME + ' = 21 GROUP BY ' + NAME + '; '
FROM SYSCOLUMNS 
WHERE ID = OBJECT_ID(@TABLENAME) 
AND	LEFT(NAME, 1) LIKE 'R%'

EXEC(@SQL_STETMENT)

__________________
Make love not war!

JSanderson
Starting Member

3 Posts

Posted - 2003-10-21 : 14:59:27

My apologies to both you and Raymond... it appears I provided a poor explanation of what I need to do, as both your solutions provide a similar result.

The result I'm trying to end up with is a derived column that contains a count (on a per-row basis) of how many times a given value (21, for example) appears in any columns beginning with 'r' (the number of 'r' columns will grow/change over time).

If this is the data...

B1 R1 R2 R3
-- -- -- --
AA 1 21 NULL
BC 21 18 21
CC 47 35 17
CD NULL NULL NULL
DG 0 21 NULL
HK 21 21 21

then the result set I need to create is...

B1 COUNT of 21's
-- -------------
AA 1
BC 2
CC 0
CD 0
DG 1
HK 3

I'm not sure what you mean by a 'homework question', but I genuinely appreciate your replies.

Thanks again.
Jim.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-21 : 15:14:24

You should normalize your data. Your tables should not be structured such that the # of columns they contain change over time. Data should be stored in rows, not columns.

If you normalize your data, the answer to this problem is very easy. YOu also get rid of all those nulls.

i.e., instead of

B1 R1 R2 R3
-- -- -- --
AA 1 21 NULL
BC 21 18 21
CC 47 35 17
CD NULL NULL NULL
DG 0 21 NULL
HK 21 21 21

you store:

BB,RR, Value
--,--,----
AA,1,1
AA,2,21
BC,1,21
BC,2,18
BC,3,21
CC,1,47
CC,2,35
CC,3,17
DG,1,0
DG,2,21
HK,1,1
HK,2,21
HK,3,21

then your result is simply:

Select BB, Count(*)
From yourtable
where Value = 21

easy as pie! and you are not storing nulls over and over every where or changing your table structures as new "R" 's appear in your data.

- Jeff

JSanderson
Starting Member

3 Posts

Posted - 2003-10-21 : 16:33:57

Hi Jeff.

I was wondering if someone might mention that. I'm only showing this small chunk of the picture to keep my description of the problem as simple as possible.

It is in fact our need/goal in this situation to DE-normalize...
beginning with a relational model, we're building a table that rolls up multiple values to the customer level (a row for each customer in a single table that will be used by other apps to do analytical exercises, etc). The 'r' columns contain codes are representative of different types of customer behaviour over time.

Thanks.
Jim.

quote]Originally posted by jsmith8858

You should normalize your data. Your tables should not be structured such that the # of columns they contain change over time. Data should be stored in rows, not columns.

If you normalize your data, the answer to this problem is very easy. YOu also get rid of all those nulls.

i.e., instead of

B1 R1 R2 R3
-- -- -- --
AA 1 21 NULL
BC 21 18 21
CC 47 35 17
CD NULL NULL NULL
DG 0 21 NULL
HK 21 21 21

you store:

BB,RR, Value
--,--,----
AA,1,1
AA,2,21
BC,1,21
BC,2,18
BC,3,21
CC,1,47
CC,2,35
CC,3,17
DG,1,0
DG,2,21
HK,1,1
HK,2,21
HK,3,21

then your result is simply:

Select BB, Count(*)
From yourtable
where Value = 21

easy as pie! and you are not storing nulls over and over every where or changing your table structures as new "R" 's appear in your data.

- Jeff
[/quote]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-21 : 16:44:38

Then what is the problem? just select from your tables before you de-normalize the data.

if you want to "roll up" multile rows into 1 column then that's great, but don't try to then analyze that data ! analyze the raw data! you can always join them together to return 1 result.

- Jeff

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-10-22 : 04:38:33

quote:

I'm not sure what you mean by a 'homework question', but I genuinely appreciate your replies.

Jim,

the question sounded like an exam question. It's the way you phrase it!

Hope you find a working solution!
Good luck.

__________________
Make love not war!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources