Overcoming the Limitation of PIVOT for Strings

This example will use the following table:SELECT Record, Variable, VariableValue
FROM #temp123Here is a resultset from the above SELECT query:

Record	Variable	VariableValue
1	1	First Value
1	2	Second Value
1	3	Third Value
1	4	Fourth Value
2	1	First Value
2	2	Second Value
2	3	Third Value
2	7	Seventh Value
2	8	Eighth Value
3	9	Ninth Value
4	2	Second Value
4	5	Fifth Value
4	10	Tenth Value

Now we can use the PIVOT Keyword to create a crosstab result. This will make columns for 'Variable': 1,2,3,4,5,6,7,8. A single row will be displayed for each record.
Note: We are not making columns for 'Variable' 9 and 10.

SELECT *
FROM #temp123
PIVOT
(
  MAX(VaribleValue)
  FOR [Variable] IN ([1],[2],[3],[4],[5],[6],[7],[8])
)
AS pHere is a resultset from the above PIVOT query:

Record	1	2	3	4	5	6	7	8
1	First Value	Second Value	Third Value	Fourth Value	null	null	null	null
2	First Value	Second Value	Third Value	null	null	null	Seventh Value	Eighth Value
3	null	null	null	null	null	null	null	null
4	null	Second Value	null	null	Fifth Value	null	null	null

Dynamic PIVOT with Grand Total Row - PIVOT without specifying columns

This is the same as the above dynamic pivot, except it also adds a Grand Total Row to the dataset. This was created in response to a user's question.
DECLARE @columns VARCHAR(8000)
DECLARE @TotalColumns VARCHAR(8000)

SELECT
@columns = COALESCE(@columns + ',[' + cast(Variable as varchar) + ']',
'[' + cast(Variable as varchar)+ ']'),

@TotalColumns =
COALESCE(@TotalColumns + ',SUM([' + cast(Variable as varchar) + ']) as [' + cast(Variable as varchar) + ']',
'SUM([' + cast(Variable as varchar)+ ']) as [' + cast(Variable as varchar)+ ']')

FROM #temp123
GROUP BY Variable

DECLARE @query VARCHAR(8000)
SET @query = '
SELECT *
FROM #temp123
PIVOT
(
MAX(VaribleValue)
FOR [Variable]
IN (' + @columns + ')
)
AS p
UNION
SELECT ''Grand Total'',' + @TotalColumns + '
FROM #temp123
PIVOT
(
MAX(VaribleValue)
FOR [Variable]
IN (' + @columns + ')
)
AS total
'

EXECUTE(@query)
The above script will first define two variables for storing the new column list and the Total select columns.
The next step will select each 'Variable' field and append it to the @columns variable, it will also place brackets around each field.
It will also select each 'Variable' field and append it to the @TotalColumns variable with the SUM() function around it as well as specifying the name of the field.
The next step defines the variable for storing the query.
The fourth step builds the query string and stores it in the query variable
The last step runs the query using the EXECUTE function

Here is a resultset from the above Dynamic PIVOT query:

Country	Sales	Expenses	Taxes	Profit
North America	2000000	1250000	250000	500000
Europe	2500000	1250000	500000	750000
South America	500000	250000	null	null
Asia	800000	350000	100000	null
Grand Total	5800000	3100000	850000	1250000