INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

T-SQL Hints and Tips

Concatenating Row Values into 1 Record/String by Catadmin
Posted: 16 Nov 06

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:

Quote (Nameless):


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:

CODE

--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:

CODE


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


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

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.

Good luck with your coding!!  I hope this helps.


Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close