June 5, 2013 at 1:59 pm
I have a sql server database table with columns as shown below :
Table1
Id Name ErrorCodes
1 AB
2 CD
3 AB 39
4 AB 40
I want to get an output something like this :
Name IdCount ErrorIdCount IdswthErrorCodes
AB 3 2 3,4
CD 1 0 0
I wrote a query which looks like this currently :
select Name, Count(Id) as IdCount,
Count(Distinct case when ErrorId != ' ' then Id END) as ErrorIdCount
from Table1
group by Name;
It gives me something like this below :
Name IdCount ErrorIdCount.
AB 3 2
CD 1 0
I cannot figure out how I can include theIdswthErrorCodes too in my query ? Can anyone point me out how I can solve this ?
June 5, 2013 at 2:03 pm
--edit--
nvm I misread the question. I will toss together something that can do this shortly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 5, 2013 at 2:14 pm
Sure !! I guess you didnt miss read it . I edited my question 🙂
June 5, 2013 at 2:16 pm
I think I read it after you edited it. 😉
At any rate it would be very helpful if in the future you could post ddl and sample data. I put this together for you to show you what I mean.
The following query produces the results as you stated you wanted in your sample data.
if OBJECT_ID('tempdb..#Table1') is not null
drop table #Table1
create table #Table1
(
ID int,
Name char(2),
ErrorCode int
)
insert #Table1
select 1, 'AB', null union all
select 2, 'CD', null union all
select 3, 'AB', 39 union all
select 4, 'AB', 40
select Name, isnull(x.ErrorCount, 0) as ErrorCount,
isnull(STUFF((select ',' + cast(ErrorCode as varchar(5))
from #Table1 t2
where t1.Name = t2.Name
order by ErrorCode
FOR XML PATH('')), 1, 1, ' '), '0')
from #Table1 t1
cross apply (select COUNT(ErrorCode) as ErrorCount from #Table1 t2 where t2.Name = t1.Name and t1.Name is not null) x
where t1.ErrorCode is null
group by Name, isnull(x.ErrorCount, 0)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 5, 2013 at 2:19 pm
Try this:
declare @TestTab table ( -- Table variable to hold sample data
Id int, -- Not really sure what data type is being used but this works
Name char(2), -- probably not right but fits the sample data
ErrorCOdes char(2) -- Again, have to guess here
);
insert into @TestTab
values (1,'AB',''),
(2,'CD',''),
(3,'AB','39'),
(4,'AB','40');
select
tt1.Name,
Count(tt1.Id) as IdCOunt,
Count(distinct case when tt1.ErrorCodes <> ' ' then tt1.Id else null end) as ErrorIdCount,
isnull(stuff((select ',' + case when tt2.ErrorCodes <> ' ' then tt2.ErrorCodes else null end
from @TestTab tt2
where tt1.Name = tt2.Name
order by ErrorCodes
for xml path(''),TYPE).value('.','varchar(max)'),1,1,''),'') as IdsErrorCodes
from
@TestTab tt1
group by
tt1.Name;
June 6, 2013 at 8:09 am
Hey Sean ,
The query you have gives a solution like this :
NameErrorCount(IdswithErrorCodes)
AB2 39,40
CD0 0
but I am looking for a solution like this :
Name ErrorCount IdswthErrorCodes
AB 2 3,4
CD 0 0
Is there a way I can list the Id's with Errorcodes instead of the error codes themselves ?
June 6, 2013 at 1:39 pm
What you had was VERY close. Both Lynn and I had a solution that returned the values instead of IDs. I have to say that returning the IDs seems very bizarre but whatever.
I modified both my code and Lynn's to it returns the output you requested.
First the changes to mine:
if OBJECT_ID('tempdb..#Table1') is not null
drop table #Table1
create table #Table1
(
ID int,
Name char(2),
ErrorCode int
)
insert #Table1
select 1, 'AB', null union all
select 2, 'CD', null union all
select 3, 'AB', 39 union all
select 4, 'AB', 40;
select Name, isnull(x.ErrorCount, 0) as ErrorCount,
isnull(STUFF((select ',' + case when t2.ErrorCode <> ' ' then cast(t2.Id as varchar(4)) else null end
from #Table1 t2
where t1.Name = t2.Name
order by ErrorCode
FOR XML PATH('')), 1, 1, ''), '0') as ErrorCodes
from #Table1 t1
cross apply (select COUNT(ErrorCode) as ErrorCount from #Table1 t2 where t2.Name = t1.Name and t1.Name is not null) x
where t1.ErrorCode is null
group by Name, isnull(x.ErrorCount, 0)
And now Lynn's. His actually required less modification because he already had the case expression inside the stuff.
declare @TestTab table ( -- Table variable to hold sample data
Id int, -- Not really sure what data type is being used but this works
Name char(2), -- probably not right but fits the sample data
ErrorCOdes char(2) -- Again, have to guess here
);
insert into @TestTab
values (1,'AB',''),
(2,'CD',''),
(3,'AB','39'),
(4,'AB','40');
select
tt1.Name,
Count(tt1.Id) as IdCOunt,
Count(distinct case when tt1.ErrorCodes <> ' ' then tt1.Id else null end) as ErrorIdCount,
isnull(stuff((select ',' + case when tt2.ErrorCodes <> ' ' then cast(tt2.Id as varchar(4)) else null end
from @TestTab tt2
where tt1.Name = tt2.Name
order by ErrorCodes
for xml path(''),TYPE).value('.','varchar(max)'),1,1,''),'') as IdsErrorCodes
from
@TestTab tt1
group by
tt1.Name;
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 6, 2013 at 3:13 pm
Sean Lange (6/6/2013)
What you had was VERY close. Both Lynn and I had a solution that returned the values instead of IDs. I have to say that returning the IDs seems very bizarre but whatever.I modified both my code and Lynn's to it returns the output you requested.
First the changes to mine:
if OBJECT_ID('tempdb..#Table1') is not null
drop table #Table1
create table #Table1
(
ID int,
Name char(2),
ErrorCode int
)
insert #Table1
select 1, 'AB', null union all
select 2, 'CD', null union all
select 3, 'AB', 39 union all
select 4, 'AB', 40;
select Name, isnull(x.ErrorCount, 0) as ErrorCount,
isnull(STUFF((select ',' + case when t2.ErrorCode <> ' ' then cast(t2.Id as varchar(4)) else null end
from #Table1 t2
where t1.Name = t2.Name
order by ErrorCode
FOR XML PATH('')), 1, 1, ''), '0') as ErrorCodes
from #Table1 t1
cross apply (select COUNT(ErrorCode) as ErrorCount from #Table1 t2 where t2.Name = t1.Name and t1.Name is not null) x
where t1.ErrorCode is null
group by Name, isnull(x.ErrorCount, 0)
And now Lynn's. His actually required less modification because he already had the case expression inside the stuff.
declare @TestTab table ( -- Table variable to hold sample data
Id int, -- Not really sure what data type is being used but this works
Name char(2), -- probably not right but fits the sample data
ErrorCOdes char(2) -- Again, have to guess here
);
insert into @TestTab
values (1,'AB',''),
(2,'CD',''),
(3,'AB','39'),
(4,'AB','40');
select
tt1.Name,
Count(tt1.Id) as IdCOunt,
Count(distinct case when tt1.ErrorCodes <> ' ' then tt1.Id else null end) as ErrorIdCount,
isnull(stuff((select ',' + case when tt2.ErrorCodes <> ' ' then cast(tt2.Id as varchar(4)) else null end
from @TestTab tt2
where tt1.Name = tt2.Name
order by ErrorCodes
for xml path(''),TYPE).value('.','varchar(max)'),1,1,''),'') as IdsErrorCodes
from
@TestTab tt1
group by
tt1.Name;
Sean, Thanks for making the necessary changes to my code. It is appreciated.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply