Using the fancy group by and rollup functions with a combination of unpivot and pivot makes this a bit simpler. This is run on John's sample table generated above. -- unpivot, then "count" by summing a derived counter ; with cte as ( select * , SumValue = 1 from ( select * from #test1 ) src unpivot ( StatusValue for StatusColumn in ( status1, status2, status3, status4, status5 ) ) upvt ) select isnull(StatusColumn,'Total') , Verified = sum(isnull([Verified],0)) , Added = sum(isnull([Added],0)) , Changed = sum(isnull([Changed],0)) , [
www.xyz.com] = sum(isnull([
www.xyz.com],0)) , [
www.abc.com] = sum(isnull([
www.abc.com],0)) from ( select StatusColumn, StatusValue, SumValue from cte ) src pivot ( sum(SumValue) for StatusValue in ( [Verified],[Added],[Changed],[
www.xyz.com],[
www.abc.com] ) ) pvt group by rollup (StatusColumn) Note that in the final select, I sum the columns coming out of the pivot, even though this isn't necessary for the results - it is though required to allow me to use the group by rollup option. Also note that this solution is only valid for 2008 onwards. John's point about this being a pain to manage is spot on. It could be made dynamic with a bit of work though. Edit: Here's a dynamic version. As requested, it doesn't rely on specific values in the status fields. It does though force URL columns to display to the right of non-URL columns. Within the two column groups, columns will be alpha sorted left to right by name. It's simple enough to take this out if you don't need it. -- dynamic version declare @select_cols varchar(max) , @pivot_cols varchar(max) -- get the column lists ; with cte as ( select distinct StatusValue -- we use this to force column ordering to show URL values to the right of non-URL values , SortKey = case when left(StatusValue,4) = 'www.' then 2 else 1 end from ( select * from #test1 ) src unpivot ( StatusValue for StatusColumn in ( status1, status2, status3, status4, status5 ) ) upvt ) select @select_cols = stuff( ( select ',[' + StatusValue + '] = sum(isnull([' + StatusValue + '],0))' from cte where StatusValue <> '' order by SortKey, StatusValue for xml path('') ) , 1, 0, '') , @pivot_cols = stuff( ( select '[' + StatusValue + '],' from cte where StatusValue <> '' order by SortKey, StatusValue for xml path('') ) , 1, 0, '') from cte -- substitute the column lists into the query declare @sql varchar(max) select @sql = '; with cte as ( select * , SumValue = 1 from ( select * from #test1 ) src unpivot ( StatusValue for StatusColumn in ( status1, status2, status3, status4, status5 ) ) upvt ) select isnull(StatusColumn,''Total'')' + @select_cols + ' from ( select StatusColumn, StatusValue, SumValue from cte ) src pivot ( sum(SumValue) for StatusValue in (' + left(@pivot_cols,len(@pivot_cols)-1) + ') ) pvt group by rollup (StatusColumn)' -- show the resulting query print @sql -- show the query result exec(@sql)
Awesome.. This works :-) .. I have a quick question.. What if a new values suddenly shows up,e.g. '
www.somewhere.com', 'some vaule' how do i get that in another column then?
This is where the problem lies with this approach. You have to explicitly provide the values for the column headers. I'll see if I can make it dynamic, give me a while...
There are fancy functions like group by rollup and things that might can do this for you, however I am not a heavy TSQL developer type guy. Going back old school how I solved for this many years ago was doing a UNION with sums. This code should get you the end result but there may be more proper ways or efficient ways of doing the same thing. SELECT ATTRIBUTE, VERIFIED, ADDED, CHANGED, [
WWW.XYZ.COM], [
WWW.ABC.COM] FROM dbo.[Table] UNION SELECT 'TOTALS', SUM(VERIFIED), SUM(ADDED), SUM(CHANGED), SUM([
WWW.XYZ.COM]), SUM([
WWW.ABC.COM]) FROM dbo.[table]
Ah, didn't realize you could have more than one status1, status2, etc. In that case you need to sum each column and do a group by attribute such as SELECT ATTRIBUTE, SUM(VERIFIED), SUM(ADDED), SUM(CHANGED), SUM([
WWW.XYZ.COM]), SUM([
WWW.ABC.COM]) FROM dbo.[Table] GROUP BY Attribute UNION SELECT 'TOTALS', SUM(VERIFIED), SUM(ADDED), SUM(CHANGED), SUM([
WWW.XYZ.COM]), SUM([
WWW.ABC.COM]) FROM dbo.[table]
I'm guessing there is a much more elegant solution to this, however, I did it in this fashion: CREATE TABLE #test1 (name VARCHAR(25), status1 VARCHAR(15), status2 VARCHAR(15), status3 VARCHAR(15), status4 VARCHAR(15), status5 VARCHAR(15)) INSERT #test1 (name, status1, status2, status3, status4, status5) SELECT 'Arjun', 'Changed', 'Verified', 'Verified', 'Added', '
www.abc.com' UNION SELECT 'Arun', '
www.abc.com', 'Changed', 'Added', 'Added','Changed' UNION SELECT 'Ashok', 'Verified', 'Verified', 'Changed', 'Changed', 'Changed' UNION SELECT 'Divya', '
www.xyz.com', 'added', 'verified', '
www.abc.com', 'verified' UNION SELECT 'John', 'Verified', 'Changed', 'Added', 'Verified', 'Verified' UNION SELECT 'Manoj', 'Added', 'Verified', '
www.abc.com','Changed', 'Changed' UNION SELECT 'Ranjitha', 'Added', 'Added', 'Verified', 'Verified', 'Verified' UNION SELECT 'Usha', '', 'Verified', 'Changed', 'Changed', 'Changed' UNION SELECT 'Xavier', 'Added', '
www.abc.com', 'Added', 'Verified', 'Verified' CREATE TABLE #results (a char(1) default 1, statustxt varchar(15),Verified INT, added INT, changed INT, [
www.xyz.com] INT , [
www.abc.com] INT) INSERT INTO #results (statustxt, Verified, added, changed, [
www.xyz.com], [
www.abc.com]) SELECT 'status1', [Verified], [Added], [Changed], [
www.xyz.com], [
www.abc.com] FROM (SELECT status1 FROM #test1 ) AS sourcetable PIVOT (COUNT(status1) FOR status1 in ([Verified], [Added], [Changed], [
www.xyz.com], [
www.abc.com]) ) as pt1 UNION SELECT 'status2', [Verified], [Added], [Changed], [
www.xyz.com], [
www.abc.com] FROM (SELECT status2 FROM #test1 ) AS sourcetable PIVOT (COUNT(status2) FOR status2 in ([Verified], [Added], [Changed], [
www.xyz.com], [
www.abc.com]) ) as pt1 UNION SELECT 'status3', [Verified], [Added], [Changed], [
www.xyz.com], [
www.abc.com] FROM (SELECT status3 FROM #test1 ) as sourcetable PIVOT (COUNT(status3) FOR status3 in ([Verified], [Added], [Changed], [
www.xyz.com], [
www.abc.com]) ) as pt1 UNION SELECT 'status4', [Verified], [Added], [Changed], [
www.xyz.com], [
www.abc.com] FROM (SELECT status4 FROM #test1 ) as sourcetable PIVOT (COUNT(status4) FOR status4 in ([Verified], [Added], [Changed], [
www.xyz.com], [
www.abc.com]) ) as pt1 UNION SELECT 'status5', [Verified], [Added], [Changed], [
www.xyz.com], [
www.abc.com] FROM (SELECT status5 FROM #test1 ) as sourcetable PIVOT (COUNT(status5) FOR status5 in ([Verified], [Added], [Changed], [
www.xyz.com], [
www.abc.com]) ) as pt1 SELECT statustxt, Verified, added, changed, [
www.xyz.com],[
www.abc.com] FROM #results UNION SELECT 'total', SUM(verified), SUM(added), SUM(changed), SUM([
www.xyz.com]), SUM([
www.abc.com]) FROM #results GROUP BY a One of the beauties of T-SQL is there is always another way to get things done. ;-) This solution, however, is NOT dynamic so if you have additional columns or status values, this type of coding will become very ugly very quickly I would imagine. I could also be way off base in this solution. If there is a better way, please someone post so that I can learn too! Hope that helps some!
![New Input File][1] ![New Output Result][2] [1]: /storage/temp/2327-input1.jpg [2]: /storage/temp/2328-output1.jpg This is a question by the way.. How to count and sum values of multiple column - Team wise
Hi #iainrobertson, I have a similar question posted in a separate thread. Below is the link. All i have added is team name. How do i get the sum or a count by team wise.
https://ask.sqlservercentral.com/questions/119477/how-to-count-and-sum-values-of-multiple-column-tea.html
Can anyone assist me on this?
17 People are following this question.
