SQL to concatenate many rows into a single string

I used this SQL script to convert multiple returned rows into a single text string in SQL Server 2008.
SELECT REPLACE(
(SELECT CASE TBL.DayNumber
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Tue'
WHEN 4 THEN 'Wed'
WHEN 5 THEN 'Thu'
WHEN 6 THEN 'Fri'
WHEN 7 THEN 'Sat'
ELSE NULL
END AS 'data()' 
FROM [dbo].[MyTableName] TBL
FOR XML PATH (''))
, ' ', ',') AS CommaDelimDayNames

This will convert:
Id          DayNumber
----------- --------------------
77          4
77          5
77          6

into:
Id          CommaDelimDayNames
----------- --------------------
77          Wed,Thu,Fri


As usual I had inspiration from the wonderful stackoverflow, in particular Diwakar's answer:
http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string/5580166#5580166
This has the answer:

REPLACE((select FName AS 'data()'  from NameList  for xml path('')), ' ', ', ')

My hg/mercurial settings

My .hgignore for Visual Studio projects:


# use glob syntax
syntax: glob


# Ignore Visual Studio files
*.obj
*.exe
*.pdb
*.user
*.vspscc
*.bak
*.cache
*.ilk
*.log
*.sbr
*.scc
[Bb]in
[Dd]ebug*/
obj/
[Rr]elease*/
_ReSharper*/
[Tt]est[Rr]esult*
[Bb]uild[Ll]og.*
*.[Pp]ublish.xml


#*.suo # depending on public/private repo

SQL Server PIVOT and UNPIVOT to transpose a table

I had to rotate/transpose a SQL table variable for reporting purposes and eventually got SQL Server 2008 PIVOT and UNPIVOT operators to achieve this (rather that using a longer UNION ALL approach.)

Source table definition:
DECLARE @ResultTable TABLE
(
Ordinal INT,
Calc1 FLOAT,
Calc2 FLOAT,
Calc3 FLOAT
)

SELECT * FROM @ResultTable:
1 | 1000000 | 0.438556290234799 | 3.53674427608709
2 | 0 | 0.877112580469597 | 7.07348855217417
3 | 1000000 | 0.877112580469597 | 7.07348855217417
4 | 0 | 1.3156688707044 | 10.6102328282613
5 | 1000000 | 1.3156688707044 | 10.6102328282613
6 | 0 | 0 | 0

SQL to rotate/transpose rows to columns:
;WITH CTE AS 
  (
    SELECT Ordinal, Col1 as SeriesName, Col as Value FROM (SELECT * FROM @ResultTable) P
      UNPIVOT (Col FOR Col1 IN (
        Calc1, Calc2, Calc3 -- Series to return as rows
      )) UN
  )
  SELECT * FROM CTE
    PIVOT 
    (MAX(value) FOR Ordinal IN (
      [1],[2],[3],[4],[5],[6]  -- Ordinals to return as columns
    )) PVT

Transposed table results:
Calc1 | 1000000 | 0 | 1000000 | 0 | 1000000 | 0
Calc3 | 3.53674427608709 | 7.07348855217417 | 7.07348855217417 | 10.6102328282613 | 10.6102328282613 | 0
Calc2 | 0.438556290234799 | 0.877112580469597 | 0.877112580469597 | 1.3156688707044 | 1.3156688707044 | 0

Note that the limitation with this solution is the number rows in source table/columns in transposed table is fixed and requires SQL Server 2005+

Also thanks to Rishabh K from http://social.msdn.microsoft.com/Forums/en-AU/transactsql/thread/680023ea-bd2e-4569-a016-62810fee2001