Easy query problem

Hello Experts-
I would like a sproc to return a 1 row select with results based on the
results of what it has found. For example say the following table were
created by the sp:
ID Name Dept
23 A 4
38 B 4
117 C 4
if the sproc could tell me which of these columns contained unique values
that would be great:
ID Name Dept
null null 4
In other words if all values in a column are the same, return that value,
otherwise return null.Select (Select Case When Count(*) = 1
Then Min(ID) Else Null End
From Table T
Group By ID) As ID,
(Select Case When Count(*) = 1
Then Min(Name) Else Null End
From Table T
Group By Name) As Name,
(Select Case When Count(*) = 1
Then Min(Dept) Else Null End
From Table T
Group By Dept) As Dept
"Coffee guy" wrote:

> Hello Experts-
> I would like a sproc to return a 1 row select with results based on the
> results of what it has found. For example say the following table were
> created by the sp:
> ID Name Dept
> 23 A 4
> 38 B 4
> 117 C 4
> if the sproc could tell me which of these columns contained unique values
> that would be great:
> ID Name Dept
> null null 4
> In other words if all values in a column are the same, return that value,
> otherwise return null.|||Sorry - messed that u.. Here's the right one...
Select (Select Case When Count(Distinct ID) = 1
Then Min(ID) Else Null End
From Table T) As ID,
(Select Case When Count(Distinct Name) = 1
Then Min(Name) Else Null End
From Table T) As Name,
(Select Case When Count(Distinct Dept) = 1
Then Min(Dept) Else Null End
From Table T) As Dept
"CBretana" wrote:
> Select (Select Case When Count(*) = 1
> Then Min(ID) Else Null End
> From Table T
> Group By ID) As ID,
> (Select Case When Count(*) = 1
> Then Min(Name) Else Null End
> From Table T
> Group By Name) As Name,
> (Select Case When Count(*) = 1
> Then Min(Dept) Else Null End
> From Table T
> Group By Dept) As Dept
>
> "Coffee guy" wrote:
>|||Coffee guy wrote:
> Hello Experts-
> I would like a sproc to return a 1 row select with results based on
> the results of what it has found. For example say the following
> table were created by the sp:
> ID Name Dept
> 23 A 4
> 38 B 4
> 117 C 4
> if the sproc could tell me which of these columns contained unique
> values that would be great:
> ID Name Dept
> null null 4
> In other words if all values in a column are the same, return that
> value, otherwise return null.
<snort>
What makes you think this query is "Easy"?
Try this:
CREATE TABLE #temp (
ID int,
Name varchar(10),
Dept int)
insert into #temp
select 23,'A',4
union all select 38,'B',4
union all select 117,'C',4
SELECT
(SELECT TOP 1 CASE WHEN
(SELECT COUNT(DISTINCT ID) FROM #temp)=1 THEN
ID
END FROM #temp) ID
,(SELECT TOP 1 CASE WHEN
(SELECT COUNT(DISTINCT Name) FROM #temp)=1 THEN
Name
END FROM #temp) Name
,(SELECT TOP 1 CASE WHEN
(SELECT COUNT(DISTINCT Dept) FROM #temp)=1 THEN
Dept
END FROM #temp) Dept
drop table #temp
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks to both, harder than I thought ;)
"Bob Barrows [MVP]" wrote:

> Coffee guy wrote:
> <snort>
> What makes you think this query is "Easy"?
> Try this:
> CREATE TABLE #temp (
> ID int,
> Name varchar(10),
> Dept int)
> insert into #temp
> select 23,'A',4
> union all select 38,'B',4
> union all select 117,'C',4
> SELECT
> (SELECT TOP 1 CASE WHEN
> (SELECT COUNT(DISTINCT ID) FROM #temp)=1 THEN
> ID
> END FROM #temp) ID
> ,(SELECT TOP 1 CASE WHEN
> (SELECT COUNT(DISTINCT Name) FROM #temp)=1 THEN
> Name
> END FROM #temp) Name
> ,(SELECT TOP 1 CASE WHEN
> (SELECT COUNT(DISTINCT Dept) FROM #temp)=1 THEN
> Dept
> END FROM #temp) Dept
> drop table #temp
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||I think this is a bit simpler than what's been posted so far. Assuming
no NULLs in any of the columns,
select
case when min(ID) = max(ID) then min(ID) else null end as ID,
case when min(Name) = max(Name) then min(Name) else null end as Name,
case when min(Dept) = max(Dept) then min(Dept) else null end as Dept
from #temp
Steve Kass
Drew University
Coffee guy wrote:
>Thanks to both, harder than I thought ;)
>"Bob Barrows [MVP]" wrote:
>
>|||Duh! I definitely did not give this enough thought.
Thanks,
Bob
Steve Kass wrote:
> I think this is a bit simpler than what's been posted so far. Assuming no
> NULLs in any of the columns,
> select
> case when min(ID) = max(ID) then min(ID) else null end as ID,
> case when min(Name) = max(Name) then min(Name) else null end as Name,
> case when min(Dept) = max(Dept) then min(Dept) else null end as Dept
> from #temp
>
> Steve Kass
> Drew University
> Coffee guy wrote:
>
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Steve,
Yes, Elegant !
"Steve Kass" wrote:

> I think this is a bit simpler than what's been posted so far. Assuming
> no NULLs in any of the columns,
> select
> case when min(ID) = max(ID) then min(ID) else null end as ID,
> case when min(Name) = max(Name) then min(Name) else null end as Name,
> case when min(Dept) = max(Dept) then min(Dept) else null end as Dept
> from #temp
>
> Steve Kass
> Drew University
> Coffee guy wrote:
>
>|||And if your fingers are tired, these are a tiny bit shorter,
but they're basically the same thing:
select
nullif(min(ID),nullif(min(ID), max(ID))) as ID,
nullif(min(Name),nullif(min(Name), max(Name))) as Name,
nullif(min(Dept),nullif(min(Dept), max(Dept))) as Dept
from #temp
select
case min(ID) when max(ID) then min(ID) end as ID,
case min(Name) when max(Name) then min(Name) end as Name,
case min(Dept) when max(Dept) then min(Dept) end as Dept
from #temp
SK
CBretana wrote:
>Steve,
>Yes, Elegant !
>
>"Steve Kass" wrote:
>
>sql

dynamic table columns

I have a sproc that returns sales amount for each month in a given range of
dates. The date range is over the last 8 years, and will grow automatically
as time goes by.
I really do not want to create a table in RS with over 120 columns of data
and then set each of them to display/hide based on the parameters passed.
Matrix table doesnt seem to work for me as I dont want to un-pivot my data
just so it can pivot it back out.
Is there some solution to this problem? I have 5 base columns that will show
regardless of what yeear/month is passed, and usually anywhere from 12 to 48
result columns. is there a way for the table to recognize how many columns
are in the result set and create enough columns automagically?
Thanks!!On Dec 6, 4:03 pm, Carl Henthorn
<CarlHenth...@.discussions.microsoft.com> wrote:
> I have a sproc that returns sales amount for each month in a given range of
> dates. The date range is over the last 8 years, and will grow automatically
> as time goes by.
> I really do not want to create a table in RS with over 120 columns of data
> and then set each of them to display/hide based on the parameters passed.
> Matrix table doesnt seem to work for me as I dont want to un-pivot my data
> just so it can pivot it back out.
> Is there some solution to this problem? I have 5 base columns that will show
> regardless of what yeear/month is passed, and usually anywhere from 12 to 48
> result columns. is there a way for the table to recognize how many columns
> are in the result set and create enough columns automagically?
> Thanks!!
I guess I'm failing to see why you have to return all of the data
unaggregated to RS. Why not just limit the amount of months by
passing the correct parameters to your stored procedure? Then you
could just let the Matrix do its job and dynamically create the
columns.
If you absolutely have to do it this way, go to the Matrix Properties,
Filter tab, and add some log here to determine which of the Months
(that Group) you want to see. This will have the same effect as
setting the Visible on the columns. It will not speed up the data
fetch portion of your report.
If you could provide a little more detail as to what you are actually
trying to do, it would be helpful. What kind of groupings do you have
in the rows, since Months are your columns? Are you manually creating
multiple Rows, each with a different field in your dataset? There are
many ways to get to the same outcome.
-- Scott