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
as to what a XSD Schema is, what an