September 16, 2009 at 9:38 am
Test table:
create table #t1(id int, colorCode1 varchar(1), colorCode2 varchar(1))
insert into #t1
select 1,'G', 'G'
union all
select 2,'G', 'W'
union all
select 3,'G', 'G'
union all
select 4,'G', 'Y'
union all
select 5,'G', 'R'
union all
select 6,'G', 'O'
select * from #t1
drop table #t1
Is it possible to remove a column from the result set based on the values contained? Like in the test table above, if I get all "G"s in my result set, I don't want to return that column (colorCode1) to the front end. I was wondering that if it can be done through a single clause in TSQL then I won't have to do any manipulation at the front-end.
Thanks.
September 16, 2009 at 10:16 am
September 16, 2009 at 10:58 am
Hmm. I won't take that route then. I can do the required in front-end, and I'll have to run a loop for the number of columns and it's not that costly. I just thought I'll see if there is anything simple that can do the thing in one shot.
Thanks for your time.
September 16, 2009 at 11:32 am
Not the most efficient method, but you may want to try this:
SELECT DISTINCT colorCode1 FROM #T1
IF @@RowCount = 1
BEGIN
SELECT id,colorCode2 FROM #T1
END
ELSE
SELECT * FROM #T1
September 16, 2009 at 11:49 am
Bitbucket is hinting at the second way to do this, that I disregarded (possibly prematurely) because it's not dynamic. You need to know what every possible combination of columns is ahead of time. Not difficult if your actual situation is as simple as your example, but I assumed that you were working with a lot more than 2 columns(And just supplied 2 to demonstrate your question), which makes that method just shy of impossible.
The issue is that you cannot use a variable as a list of columns to select unless you use dynamic SQL. If you could, this wouldn't be an issue, you'd just pull a list of columns, check each one of them for uniqueness and build a list of those columns for your select.
September 16, 2009 at 1:49 pm
Thank you @Bitbucket for the suggestion but as @Garadin pointed out the situation is not as simplistic. I'd the test data to give an idea of what I'm trying to achieve.
I'm not averse to dynamic SQL (actually my query already is as I'm using PIVOT & getting the column names dynamically) but in this condition I'm not sure if with this addition my query will be as neat as I would like it to be. As I understand from the suggestion it appears that I'll have to generate dynamic code that will do a DISTINCT for each of the columns (and that's why I'm not inclined towards this solution).
I've found a solution using .Net LINQ that does the trick for me using a single loop. The advantage I see is the ease of debugging if anything doesn't look like doing what's expected(as against dynamic SQL).
The reason I posted this question was if there is anything simplistic that can achieve this that I'm missing or not aware of.
All said, thank you guys for your time and effort. Its much appreciated.
September 16, 2009 at 2:07 pm
rjv_rnjn
As I understand from the suggestion it appears that I'll have to generate dynamic code that will do a DISTINCT for each of the columns
You are correct about doing a DISTINCT for each column and that I agree can get messy, as tangled a a bowl of pasta and hard to maintain and additionally not running very fast if you have too many columns.
But I thought it might give you an idea. Good luck with your Net LINQ solution. You might want to consider posting the Net LINQ code here to help others who may have a similar problem.
September 16, 2009 at 3:49 pm
Yeah, for certain user selected options the number of columns could be as big as 36(maximum now but may grow couple years down the line).
I will surely post the .Net code (in the earlier post I didn't consider it relevant to the thread). Here it is in C#
private DataSet RemoveGreenAlerts(DataSet ds)
{
DataTable tableForEnumeration = ds.Tables[0].Copy();
foreach (DataColumn col in tableForEnumeration.Columns)
{
if (col.DataType.Equals(typeof(string)))
{
var alerts = tableForEnumeration.AsEnumerable();
var allGreens = alerts.All(alertColor => alertColor.Field(col.ColumnName) == "G");
if (allGreens)
{
ds.Tables[0].Columns.Remove(col.ColumnName);
}
}
}
return ds;
}And it's efficient for 36 columns (takes less than a millisecond).
@Bitbucket: I saw this
If everything seems to be going well, you have obviously overlooked something
and wondered what were you hinting at and a few seconds later I realized it's part of your signature. 😛
September 17, 2009 at 6:17 am
rjv_rnjn
Thank you for sharing your code. Hopefully it will assist other members of this community who have the same or a similiar problem ...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply