Validating Row contents in Table

Question

Post reply

Validating Row contents in Table

LoosinMaMind

Hall of Fame

Points: 3366
More actions
September 23, 2010 at 7:42 am

#228725

How would i go about creating the SQL script to work out the following. .........
I need to work out what % of a column is completed and with what in a table.
Below is sample data and expected results.
row key row1 row2
1 dog duck
2 dog duck
3 dog duck
4 cat cow
5 cat cow
6 cat mouse
7 mouse
8
9
10
60% 70%
results dog, cat duck,cat,mouse
Many Thanks for any replies

Viewing 6 posts - 1 through 5 (of 5 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

Some clarification please. You are apparently asking two different questions.

(1) What percentage of rows have been filled in for a particular column?

and

(2) What is the total set of values contained in both columns?

-- You have "cat" in your result twice, and "cow" not at all. Typo?

__________________________________________________

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

declare @sample table (rowkey int, row1 varchar(50), row2 varchar(50))

insert into @sample

values

(1, 'dog', 'duck'),

(2, 'dog', 'duck'),

(3, 'dog', 'duck'),

(4, 'cat', 'cow'),

(5, 'cat', 'cow'),

(6, 'cat', 'mouse'),

(7, '', 'mouse'),

(8, '', ''),

(9, '', ''),

(10, '', '')

select cast(1.0* SumRow1 /TotalRows*100 as numeric(5,2)) as Row1Pct

,cast(1.0* SumRow2 /TotalRows*100 as numeric(5,2)) as Row2Pct

,stuff((select ','+row1

from (select distinct row1 from @sample) dt

where row1 > ''

order by Row1

for xml path ('')),1,1,'') as Row1Values

,stuff((select ','+row2

from (select distinct row2 from @sample) dt

where row2 > ''

order by Row2

for xml path ('')),1,1,'') as Row2Values

from (select COUNT(*) as TotalRows

, SUM(case when row1 > '' then 1 else 0 end) as SumRow1

, SUM(case when row2 > '' then 1 else 0 end) as SumRow2

from @sample) dt

-- hope we get an "A"

__________________________________________________

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

LoosinMaMind Hall of Fame Points: 3366 More actions · Answer 3

Cheers, this works a treat, exactly what i was after. I wont be getting an A as this is for work, we are installing a Data Warehouse and I'm fairly new to SQL. I will however give your good self an 'A' for your help. Thanks again.

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

You're very welcome. I would be interested in hearing the production volumes you are running through this and how it performs.

Best of luck to you.

__________________________________________________

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

He's right, it's much cleaner if you can use NULLs instead of spaces to denote absence of value.

Thank you, Mr. Celko. Or may I call you "Ming" ?? 🙂

__________________________________________________

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