SQL management studio 2005: Creating a temp pivot table

I used the following dynamic SQL to create a temp pivot table in a script to join onto my final result set. I used this method as the names of the columns could change depending on user input.
It involves creating a temp table then adding columns and populating the table using dynamic SQL in  a loop.




DECLARE @work_order int
--create temp function table first
DECLARE @function_table TABLE (primary_key INT IDENTITY(1,1) NOT NULL,
[Function] varchar(100))

DECLARE @item_category_counter INT
DECLARE @loop_counter INT

INSERT INTO @function_table
SELECT wo_team_label_desc FROM ref_wo_team WHERE dept_id = @dept_id AND
inactive_ind = 0 ORDER BY sort_order

--Pivot function table
CREATE TABLE #PIVOT_FUNCTON_TABLE (primary_key INT IDENTITY(1,1) NOT NULL,
work_order int)

SET  @loop_counter = ISNULL((SELECT COUNT(*) FROM @function_table),0)
-- Set the @loop_counter to the total number of rows in the memory table

SET @item_category_counter = 1

WHILE @loop_counter > 0 AND @item_category_counter <= @loop_counter
BEGIN

--Add dynamic column name
DECLARE @ColumnName NVARCHAR(100)
SET @ColumnName = (select [Function] FROM @function_table WHERE primary_key
= @item_category_counter)
DECLARE @query NVARCHAR(4000)
SET  @query = 'ALTER TABLE #PIVOT_FUNCTON_TABLE
ADD [' + @ColumnName + ']  NVARCHAR(100);'
EXECUTE(@query)

SET @item_category_counter = @item_category_counter + 1
END

DECLARE @item_category_counterWO INT
DECLARE @loop_counterWO INT

SET  @loop_counterWO = ISNULL((SELECT COUNT(*) FROM @WOtable),0)
-- Set the @loop_counter to the total number of rows in the memory table
SET @item_category_counterWO = 1

WHILE @loop_counterWO > 0 AND @item_category_counterWO <= @loop_counterWO
BEGIN

SET @work_order = (SELECT work_order_id FROM @WOtable WHERE primary_key =
@item_category_counterWO)

--Populate table
DECLARE @item_category_counter3 INT
DECLARE @loop_counter3 INT

SET  @loop_counter3 = ISNULL((SELECT COUNT(*) FROM @function_table),0)
-- Set the @loop_counter to the total number of rows in the memory table

SET @item_category_counter3 = 1

DECLARE @UpdateQueryHead NVARCHAR(4000)
DECLARE @UpdateQueryBody NVARCHAR(4000)
DECLARE @UpdateQueryFooter NVARCHAR(4000)
SET @UpdateQueryHead = 'INSERT INTO #PIVOT_FUNCTON_TABLE VALUES (' + (CAST
( @work_order  AS NVARCHAR(4000))) + ''
SET @UpdateQueryBody = ','
SET @UpdateQueryFooter = ')'

WHILE @loop_counter3 > 0 AND @item_category_counter3 <= @loop_counter3
BEGIN

--Create @UpdateQueryBody string
DECLARE @Value NVARCHAR(100)
SET @Value = Replace(
(SELECT RU.first_name + ' ' + RU.last_name
FROM wo_team WOT
INNER JOIN ref_wo_team rwt ON  WOT.dept_id = rwt.dept_id AND WOT.wo_team_id
= rwt.wo_team_id
INNER JOIN ref_user RU ON WOT.dept_id = RU.dept_id AND WOT.user_id = RU.
user_id
WHERE WOT.dept_id = @dept_id
AND work_order_id = @work_order
AND WOT.user_id <> ''
AND WOT.user_id <> 'NA'
AND WOT.user_id <> 'N/A'
AND wo_team_label_desc = (select [Function] FROM @function_table WHERE
primary_key = @item_category_counter3)),'''','')

SET @UpdateQueryBody = @UpdateQueryBody + (CASE WHEN @Value IS NULL THEN
'NULL' ELSE  '''' + @Value + '''' END) + ', '
SET @item_category_counter3 = @item_category_counter3 + 1

END

DECLARE @UpdateQuery NVARCHAR(4000)

--Remove trailing comma
SET @UpdateQueryBody = SUBSTRING ( @UpdateQueryBody ,1 , (len(
@UpdateQueryBody)-1))
SET @UpdateQuery = @UpdateQueryHead + @UpdateQueryBody + @UpdateQueryFooter

EXECUTE(@UpdateQuery)

SET @item_category_counterWO = @item_category_counterWO + 1
END

SQL 2005 Reporting services: Columns merging when exporting to Excel.

Came across this problem when I created a report which my users wanted to export to Excel.

The problem was that when the report was exported, Excel merged some of the columns. This caused problems for my users when they wanted to work with the data in excel (pivoting the data, etc.)

The problem was caused by the positioning of the labels in my report header. If the labels did not start or end exactly at the same position as my tables, Excel inconveniently merged two columns to cope with it.

The solution was to (with a bit of trial and error) to line up the labels with the start and end of the table. Another solution is to make your title labels the full width of your report and centre your text. This does not always work when you are working with matrix's as you can not always be sure of the width of your report. In this case line up your label with a column in your table that will always be a fixed width.

SQL 2005 Reporting services: Create alternating colours in a table or matrix (Green bar effect)

Alternating colours in a table is fairly easy and can be done by setting the background colour on the row as follows:

Iif((RowNumber(Nothing) Mod 2 = 0), "White", "WhiteSmoke")

Doing the same in a matrix is a bit more complicated. There is probably several methods out there but I find the following one to be the easiest:

First create a function in the report code as follows

Private bOddRow As Boolean
'*************************************************************************
' -- Display green-bar type color banding in detail rows
' -- Call from BackGroundColor property of all detail row textboxes
' -- Set Toggle True for first item, False for others.
'*************************************************************************
Function AlternateColor(ByVal OddColor As String, _
ByVal EvenColor As String, ByVal Toggle As Boolean) As String
If Toggle Then bOddRow = Not bOddRow
If bOddRow Then
Return OddColor
Else
Return EvenColor
End If
End Function

Next set the row colours on the matrix by setting the background colour as follows:

Code.AlternateColor( "White","WhiteSmoke", True) ----> Set this on the matrix row group

Code.AlternateColor("White","WhiteSmoke", False) -----> Set this on the cell

A combination of effects can be achieved using this method such as a checker board effect by changing the True/False values and the colours.

Back again

Wooooo, I havn't used this in a while. I oridginaly started this blog to record what I was doing in college and to record my ramblings. As you can see I sort of got side tracked lol.
So I have decided to try again. Its funny all the stuff I was reading back then. At that time I was working in the chemical industry and studying a BSC in Electronic Engineering and Computer Systems to try and enhance my career in the chemical industry. I am now working as a software engineer in the financial sector. Funny how things work out. Anyway I love what Im doing now so it all worked out well.
I'm planning to use this blog to record problems and the solutions I've come up with in software design so I can use them again. I would appreciate any comments.