Testing Mo

Q1, Q2, Q3:

 

Code:

CREATE TABLE #PovitTable
(
RowHeader varchar(15),
ColumnHeader varchar(20),
VariableValue int
)

INSERT INTO #PovitTable VALUES ('North America','Sales','2000000')
INSERT INTO #PovitTable VALUES ('North America','Expenses','1250000')
INSERT INTO #PovitTable VALUES ('North America','Taxes','250000')
INSERT INTO #PovitTable VALUES ('North America','Profit','500000')
INSERT INTO #PovitTable VALUES ('North America','Profit','200000')
INSERT INTO #PovitTable VALUES ('Europe','Sales','2500000')
INSERT INTO #PovitTable VALUES ('Europe','Expenses','1250000')
INSERT INTO #PovitTable VALUES ('Europe','Taxes','500000')
INSERT INTO #PovitTable VALUES ('Europe','Profit','750000')
INSERT INTO #PovitTable VALUES ('South America','Sales','500000')
INSERT INTO #PovitTable VALUES ('South America','Expenses','250000')
INSERT INTO #PovitTable VALUES ('Asia','Sales','800000')
INSERT INTO #PovitTable VALUES ('Asia','Expenses','350000')
INSERT INTO #PovitTable VALUES ('Asia','Taxes','100000')

--Q1
select * from #PovitTable

DECLARE @ColumnsList VARCHAR(8000)
DECLARE @RowSumList VARCHAR(max)

SELECT @ColumnsList = COALESCE(@ColumnsList + ',' + QUOTENAME(CAST(ColumnHeader as varchar)) , QUOTENAME(CAST(ColumnHeader as varchar)) )
,@RowSumList = COALESCE(@RowSumList + ' + isnull(p.' + QUOTENAME(CAST(ColumnHeader as varchar)) + ',0)',
                         'isnull(p.' + QUOTENAME(CAST(ColumnHeader as varchar))+ ',0)')                   
FROM #PovitTable
GROUP BY ColumnHeader

--Use to calculate Column Sum
select * 
into #PovitTable2
from #PovitTable
update #PovitTable2 set rowHeader = 'Total'

DECLARE @SQLQueryPovit VARCHAR(8000)
SET @SQLQueryPovit = '
SELECT *
FROM #PovitTable
PIVOT
	(
	SUM(VariableValue)
	FOR [ColumnHeader]
	IN (' + @ColumnsList + ')
	)
	AS p1
'

DECLARE @SQLQueryPovitWithTotal VARCHAR(8000)
SET @SQLQueryPovitWithTotal = '
Select *
,Total = ' + @RowSumList +'
from 
(
	SELECT *
	FROM #PovitTable
	PIVOT
		(
		SUM(VariableValue)
		FOR [ColumnHeader]
		IN (' + @ColumnsList + ')
		)
		AS p1
	 
	UNION

	SELECT *
	FROM #PovitTable2
	PIVOT
		(
		SUM(VariableValue)
		FOR [ColumnHeader]
		IN (' + @ColumnsList + ')
		)
		AS p2
) p
'

--Q2
EXECUTE(@SQLQueryPovit)

--Q3
EXECUTE(@SQLQueryPovitWithTotal)

print @SQLQueryPovit
print @SQLQueryPovitWithTotal

--檢查查詢語句
--Checking Query
print ''
print N'111 @ColumnsList				:' + @ColumnsList
print N'111 @RowSumList					:' + @RowSumList
print N'111 @SQLQueryPovit				:' + @SQLQueryPovit
print ''
print N'111 @SQLQueryPovitWithTotal		:' + @SQLQueryPovitWithTotal

DROP TABLE #PovitTable,#PovitTable2

 

 

參考:http://www.tsqltutorials.com/pivot.php

 

 

 

------------------

熱愛生命 喜愛新奇 有趣的事物

 

過去 是無法改變
將來 卻能夠創造

 

希望使大家生活更便利
世界更美好

 

a guy who loves IT and life