• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

Filtering Results from a Concatenated List

Hello :)

I have managed to pivot some data from a veritcal layout to a horizontal  concatenated view.  However, I'm having a bit of a struggle to figure out a simple and quick way to filter the results without having to put it in excel and look at each record.

Basically, we have a table with an issue_num field and then two other fields - one called column_name and one called column_value

The data looked like this:

12345.......Value_A.......0
12345.......Value_B.......1
12345.......Value_C.......0
45678.......Value_A.......-
45678.......Value_B.......-
98765.......Value_A.......0
98765.......Value_B.......0
98765.......Value_C.......0
98765.......Value_D.......0

And it goes on and on of course...the number of variations in the column_name field I think can go up to like "Value_Z" and its totally varied how many values 1 issue_num may have.

So, what I did was create a nice query that puts it vertically for me like so:

12345......0,1,0
45678......-,-
98765......0,0,0,0

Now, the thing I'm struggling with since my concatenated field will never have the same number of values and could look something like this in addition to what I have shown: 0,4,2,1,-,-,-,1,2,3,4 ... and I need to pull out all issue numbers that have either a 0 across the board or a "-" across the board.  

So, in my example, I would want my query to return 45678 and 98765 because those do not have any valid values in each column value.

I tried messing with a complicated where clause but it just got ugly and I even pasted the result set into excel and thought about manually looking at each one but I know there has to be a programmatic way for me to do this, I'm just having a brain fart.

Any help is appreciated. :)
Please let me know if you need more information.
0
Roxanne25
Asked:
Roxanne25
  • 3
  • 2
1 Solution
 
John_VidmarCommented:
There are more query-techniques available if your data is normalized, combining multiple rows into a single field will not help you.

Assuming column_name (has a values like 12345, 45678) and column_value (has character values like 0, 1, -) :
select	column_name
from	#sometable
group
by	column_name
having	COUNT(*) = SUM(case when column_value in ('0','-') then 1 else 0 end)

Open in new window

0
 
Roxanne25Author Commented:
Hi thanks, the combining of the rows is exactly what I "want" it to do ... I just need to find instances where the values are all 0's or all -'s ... I tried your query but it did not return any data.

The original data set looks exactly as I have it  like this:

issue_num....column_name....column_value
12345.......Value_A.......0
12345.......Value_B.......1
12345.......Value_C.......0
45678.......Value_A.......-
45678.......Value_B.......-
98765.......Value_A.......0
98765.......Value_B.......0
98765.......Value_C.......0
98765.......Value_D.......0

and its the column_value and the issue_num that I care about not the column_name.  The part I can't get my where clause around is that the column_name field could have one value per issue_num or 20 ... if it was a consistent range IE A-E, then I could do it no problem.

So, of the original data listing that I have above... what I'm after is only issue_num 45678 and issue_num 98765 because they have 0's and -'s for every instance of column_name.  12345 does not fit into the criteria because one of the values is a 1.

If there was another way to get what I need from the original data without having to concatenate all the values then that would work too... but I'm trying not to turn this into a giant stored procedure just to get at some informational data.
0
 
John_VidmarCommented:
You have some technique to take that ugly data (which has all those periods) and parse out the values you want, and group each issue_num into one record with column_name containing its related comma-delimited list of values.

I suggest you normalize your data (first normal form, or 1NF) so each field (in this case, column_name) only contains a single value.  I used the wrong field name, but I qualified that in the assumptions.

Your data would then be in a table (I used #sometable) as follows:
      issue_num      column_name
      12345            0
      12345            1
      12345            0
      45678            -
      45678            -
      98765            0
      98765            0
      98765            0
      98765            0

So the following query, should display issue_num where all the column_name values are either 0 or dash:
select	issue_num
from	#sometable
group
by	issue_num
having	COUNT(*) = SUM(case when column_name in ('0','-') then 1 else 0 end)

Open in new window

0
 
Roxanne25Author Commented:
Well if it were possible for me to normalize the data I would... but I can't. :)
0
 
Roxanne25Author Commented:
However, I tweaked your query there a bit for the data as it is.... and I tip my hat off to you sir because you are a genius.... it works... and its so simple.  I kept looking at this like it was incredibly complicated and so it must require a complicated solution.  

THANK YOU VERY MUCH!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now