How do I take multiple records results and make the result one record?
This question, and many variations on it, have been asked frequently on this forum. The questioner provides the forum with the data and the desired result set (see below example) asking how one can combine results:
I have this record set returned for a query.
Name Phone Bobby 555-2225 Bobby 888-2495
Because Bobby has both a house phone and a cell phone, I get two records. But I only want one row with both phone #s on the same line, like below. How do I do this?
Name Phone Bobby 555-2225 / 888-2495
For something small, with a finite # of records (I.E., you're never going to have more than 2 records for each person), you can simply join the table to itself as listed below:
--This is a simple, finite table concatenation --Assumes CustID is Identity/PK/Unique identifier
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 --Second join is left outer because not all customers --will have a cell phone
Essentially, you're just re-joining the same table, CustPhone, the exact number of times you'll have records for. However, after about 3 joins, this gets tedious. It also doesn't address the possibility that someone could have 10 or 20 or 30 phones.
I've come up with a piece of code to address the possibility for "infinite" records that need to be joined together. Be warned, though, if the pre-concatenated result set is high, this query will take a LONG time to run. I've only tested it with up to seven possibilities.
In this example, I'm concatenating the columns used in Combined Primary Keys because I only want the TableName and a single string listing all the columns involved in the Primary Key. I use this code for a Data Dictionary report I'm working on:
If exists (Select * from tempdb.sys.objects where type = 'U' and [name] like '#PKConcat%') Drop Table #PKConcat --Makes sure temp table doesn't already exist in this session and deletes it if it does
Create Table #PKConcat (TableName varchar(200), PKCols varchar(800) Default '', CntCols int Default 0, RowDone bit Default 0) --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
Insert into #PKConcat (TableName) (Select Distinct [name] from sys.objects where type = 'U' and [name] <> 'dtproperties') --Inserts all user created tables into temp table. Defaults from Create statement are applied
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 --For each table name, the highest column # (ordinal_position) is taken as a value for --how many columns are involved in the Primary Key
Set @CntDown = 0 Set @TblName = '' --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
Set @TableCnt = (Select Distinct Count(TableName) from #PKConcat) --Sets the table counter with the total of table names in the temp table
While @TableCnt > 0 --while we still have tables to process Begin Set @CntDown = (Select Top 1 CntCols from #PKConcat where RowDone = 0) Set @TblName = (Select Top 1 TableName from #PKConcat where RowDone = 0) --Sets countdown value with the max # of columns for this specific table --Sets "current" table name
While @CntDown > 0 --While we still have columns to process 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 --The above Select a column name and adds it to the string with two spaces between each column --name Set @CntDown = @CntDown - 1 --Subtract 1 from our column count to make sure While Loop will actually end End
Update #PKConcat Set RowDone = 1 Where RowDone = 0 and TableName = @TblName --Set bit flag indicating this row in temp table has been processed
Set @TableCnt = @TableCnt - 1 --Subtract 1 from our table count to make sure outer While Loop will actually end End
Update #PKConcat Set PKCols = 'No Primary Key on this Table' where PKCols is NULL or PKCols = ' ' --Updates tables with a NULL or blank PKCols values to reflect there is no Primary Key available
Select TableName, ltrim(PKCols) from #PKConcat --Selects result set and trims the left-side blanks on the single column value PKCol strings
Drop Table #PKConcat --Drops the temp table
I wanted to do this without a cursor. You could use a cursor to go through the record set, but I hate doing cursors unless I have no other way of accomplishing my task.
I hope the comments in the query are clear and understandable. If they are not, please let me know. Also, the above method should work for normal record results based off of user tables such in the phone number scenario listed first.