March 6, 2014 at 6:55 am
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.
March 6, 2014 at 7:21 am
SELECT issue_num
FROM (
SELECT issue_num, column_value
FROM mydata
GROUP BY issue_num, column_value
) d
GROUP BY issue_num
HAVING COUNT(*) =1
The code bit is trivial - this is one of many ways of getting the same result set.
However, if you can provide us with a description of the whole process - pivot/unpivot/select and whatever, it's quite likely that we'll figure out a better overall method to achieve what it is you are trying to do.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 6, 2014 at 8:37 am
Hi thanks for the reply, I guess I wasn't entirely clear... I already have the data formatted how I want it which is the concatenated result set that I listed. All I'm trying to do now is filter (use a where clause) on the concatenated list to pull out issue_nums where all the concatenated values have 0's across the board or -'s across the board.
March 6, 2014 at 9:18 am
I suppose of there was another way to loop through the original data structure by issue_num and find all instances where the column_value was all 0's or all -'s for every type of column_name then that would accomplish it a different way as well.
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.
March 6, 2014 at 9:48 am
amy26 (3/6/2014)
I suppose of there was another way to loop through the original data structure by issue_num and find all instances where the column_value was all 0's or all -'s for every type of column_name then that would accomplish it a different way as well.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.
Erm, have you tried running the code I posted?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 6, 2014 at 9:58 am
Yes I did, it did not return any data. I did figure it out though from another source... I was making it incredibly more difficult than it had to be... which I think is what you were getting at as well.
selectissue_num
fromthe_table
group
byissue_num
havingCOUNT(*) = SUM(case when column_value in ('0','-') then 1 else 0 end)
March 6, 2014 at 12:59 pm
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.
Why reinvent the wheel? Use Jeff Moden's string splitter or really any string splitter to split the values into rows. Then you can do two simple queries like so
--gets 0, 0,0 0,0,0 0,0,0,0 etc.
select keyfield,valuefield from table where keyfield not in (
(select keyfield from table outer apply [dbo].[DelimitedSplit8K](valuefield,',') where item <> '0')
)
--gets -, -,- -,-,- -,-,-,- etc.
select keyfield,valuefield from table where keyfield not in (
(select keyfield from table outer apply [dbo].[DelimitedSplit8K](valuefield,',') where item <> '-')
)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply