Concatenating Row Values into 1 Record/String

[i]--This is a simple, finite table concatenation
--Assumes CustID is Identity/PK/Unique identifier[/i]

Select Name, cp.Phone + ' / ' + cp1.Phone as Phone
from CustName cn
join CustPhone cp
on cn.CustID = cp.CustID
left outer join CustPhone cp1
on cn.CustID = cp1.CustID
[i]--Second join is left outer because not all customers
--will have a cell phone[/i]

If exists (Select * from tempdb.sys.objects where type = 'U' and [name] like '#PKConcat%')
Drop Table #PKConcat
[i]--Makes sure temp table doesn't already exist in this session and deletes it if it does[/i]

Create Table #PKConcat (TableName varchar(200), 
PKCols varchar(800) Default '', CntCols int Default 0, RowDone bit Default 0)
[i]--Creates temp table for storing values.  Defaults bit value to FALSE, column string (PKCols) to blank,
--and the column count for each line (CntCols) to 0[/i]

Insert into #PKConcat (TableName)
(Select Distinct [name]
 from sys.objects
 where type = 'U'
 and [name] <> 'dtproperties')
[i]--Inserts all user created tables into temp table.  Defaults from Create statement are applied[/i]

Update pkc
Set CntCols = A.CntCols
from #PKConcat pkc
join (Select Distinct t.table_name, Max(k.Ordinal_Position) as CntCols
                    from Information_Schema.Table_Constraints t
					JOIN Information_Schema.Key_Column_Usage k
					ON t.Constraint_Name = k.Constraint_Name
					Group By t.Table_Name) A
on pkc.TableName = a.Table_Name
[i]--For each table name, the highest column # (ordinal_position) is taken as a value for
--how many columns are involved in the Primary Key[/i]

Declare @CntDown int, @TableCnt int, @TblName varchar(200)
[i]--Declare variables for below looping[/i]

Set @CntDown = 0
Set @TblName = ''
[i]--Want to make sure the countdown for the columns is initialized as zero and the tablename is
--initialized as blank so nothing weird happens in the below code[/i]

Set @TableCnt = (Select Distinct Count(TableName)
								from #PKConcat)
[i]--Sets the table counter with the total of table names in the temp table[/i]

While @TableCnt > 0  [i]--while we still have tables to process[/i]
  Begin
     Set @CntDown = (Select Top 1 CntCols from #PKConcat where RowDone = 0)
	 Set @TblName = (Select Top 1 TableName from #PKConcat where RowDone = 0)
[i]--Sets countdown value with the max # of columns for this specific table
--Sets "current" table name[/i]

     While @CntDown > 0  [i]--While we still have columns to process[/i]
        Begin
			Update pkc
			Set PKCols = ltrim(PKCols) + '  ' + (Select Distinct k.Column_Name
                                                                from Information_Schema.Table_Constraints t
																JOIN Information_Schema.Key_Column_Usage k
																ON t.Constraint_Name = k.Constraint_Name
																Where t.Table_Name = @TblName
																and k.Ordinal_Position = @CntDown
																and t.Constraint_Type = 'Primary Key')
			from #PKConcat pkc
			Where TableName = @TblName
[i]--The above Select a column name and adds it to the string with two spaces between each column
--name[/i]
			Set @CntDown = @CntDown - 1
[i]--Subtract 1 from our column count to make sure While Loop will actually end[/i]
		End
	 
	 Update #PKConcat  
	 Set RowDone = 1
	 Where RowDone = 0 and TableName = @TblName
[i]--Set bit flag indicating this row in temp table has been processed[/i]

	 Set @TableCnt = @TableCnt - 1
[i]--Subtract 1 from our table count to make sure outer While Loop will actually end[/i]	
	End

Update #PKConcat
Set PKCols = 'No Primary Key on this Table'
where PKCols is NULL
or PKCols = ' '
[i]--Updates tables with a NULL or blank PKCols values to reflect there is no Primary Key available[/i]

Select TableName, ltrim(PKCols)
from #PKConcat
[i]--Selects result set and trims the left-side blanks on the single column value PKCol strings[/i]

Drop Table #PKConcat
[i]--Drops the temp table[/i]