Click here to Skip to main content
15,843,145 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi I have a table

_____|__________|_________|_______________|____...
Total| National	| Foreign |   Category    |...
_____|__________|_________|_______________|____...
1.00 |  2.00	| 98.00	  |  Professional |...
1.00 |  2.00	| 98.00	  |  Professional |...
1.00 |  4.00	| 96.00	  |  Professional |...
2.00 |  4.00	| 96.00	  |  Marketing    |...
2.00 |  4.00	| 96.00	  |  Marketing    |...
  .  |    .     |   .     |     .         | .
  .  |    .     |   .     |     .         | .
  .  |    .     |   .     |     .         | .


I want the output in the form


_______________________________________________...
              |Professional	|Marketing|...
______________|_________________|_________|____...
Total 	      |		        |         |...
Foreign	      |                 |         |...
National      |			|         |...


the query that i have written

SQL
DECLARE @cols AS VARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Category)
            FROM Service
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)')
        ,1,1,'')

--print @cols

set @query = 'SELECT * FROM
                            (
                                SELECT
                                        [Total],
                                        [Nationally],
                                        [Foreign],
                                        [Category]
                                FROM
                                        Service
                            ) AS source
                        PIVOT
                            (
                                AVG([Total])
                                FOR [Category] IN ('+@cols+')
                            ) p'
execute( @query)



This query is not working properly
Please Help
thanks in advance
Posted
Updated 16-Mar-14 8:55am
v2

1 solution

You need to UNPIVOT[^] data, than to use aggregate functions[^] ;)

See below example:
SQL
DECLARE @tmp TABLE (Total DECIMAL(8,2), [National] DECIMAL(8,2), [Foreign]  DECIMAL(8,2), Category VARCHAR(30))

INSERT INTO @tmp (Total, [National], [Foreign], Category )
VALUES(1.00 ,  2.00	, 98.00, 'Professional'),
(1.00 ,  2.00	, 98.00, 'Professional'),
(1.00 ,  4.00	, 96.00, 'Professional'),
(2.00 ,  4.00	, 96.00, 'Marketing'),
(2.00 ,  4.00	, 96.00, 'Marketing')

--aggregate data
SELECT Category, Description, SUM(Value) AS SumOfValue
FROM (
	--unpivot data
	SELECT Category, [Description], [Value]
	FROM (
		SELECT *
		FROM @tmp
		) AS pvt
	UNPIVOT ([Value] FOR [Description] IN ([Total],[National], [Foreign])) AS unpvt
	) AS T
GROUP BY Category, [Description]
ORDER BY Category, [Description]


Result:
Category        Description     SumOfValue
Marketing	Foreign		192.00
Marketing	National	8.00
Marketing	Total		4.00
Professional	Foreign		292.00
Professional	National	8.00
Professional	Total		3.00


If you don't want to display Category, remove it from first SELECT list and GROUP BY option.


Of course, it is possible to pivot data again:
SQL
--pivot data
SELECT [Description], [Professional], [Marketing]
FROM (
	--unpivot data
	SELECT Category, [Description], [Value]
	FROM (
		SELECT *
		FROM @tmp
		) AS pvt
	UNPIVOT ([Value] FOR [Description] IN ([Total],[National], [Foreign])) AS unpvt
	) AS DT
PIVOT(SUM([Value]) FOR [Category] IN ([Professional], [Marketing])) AS PT

Than result is:
Category	Professional	Marketing
Foreign		292.00		192.00
National	8.00		8.00
Total		3.00		4.00
 
Share this answer
 
v3
Comments
write2varun 16-Mar-14 13:57pm    
Thanks for the quick answer, but I need the table format as table 2
please help and sorry it was AVG in place of MAX
Maciej Los 16-Mar-14 13:59pm    
See my answer again.
write2varun 16-Mar-14 14:17pm    
Thanks brother that will solve my problem, but I have a doubt the category will increase and I want to use a variable so can i give like something like "SELECT [Description], @colms
FROM " inplace of "[Professional], [Marketing]"
Maciej Los 16-Mar-14 14:28pm    
You need to mix my solution with yours ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900