Convert Rows to Column (singular)

Business Case: Text fields are used to record information ancilary to sales orders, purchase orders, customer records, and various other data sets. A user has requested that these notes be displayed at the header level of the related parent data record.

Environment: The complication with this request is that the text field used to store the notes is a child object to the parent. Thus, for each sales order (purchase order, customer record, etc.) there can be any number of child records, each with a string note. A note sequence indicator exists on the child record such that note records are kept in order in the event a single logical note spans multiple physical rows. The users require there to be only a single record per parent, with the notes concatenated and displayed as a column of the single parent record.

Approach: We will use a FOR XML clause to compress row data into a delimited text string. In combination with a CROSS APPLY join we can then return the string to the user as a single column with each parent record.

Demonstration Preparation: For demonstration purposes, first we will simulate the underlying data structure by creating a parent table called Records and a child table called Notes, related to each other through a foreign key. For this example we will assume our database is named “DB” and will be working within the “dbo” schema.

-- If the child table foreign key exists, drop it
IF OBJECT_ID('DB.dbo.FK_Notes_Records') IS NOT NULL
      ALTER TABLE DB.dbo.Notes
      DROP CONSTRAINT FK_Notes_Records
;

-- If the parent table exists, drop it
IF OBJECT_ID('DB.dbo.Records') IS NOT NULL
      DROP TABLE DB.dbo.Records
;

-- Create the parent table
CREATE TABLE DB.dbo.Records (
      ID INTEGER IDENTITY(1,1) NOT NULL
      , Label VARCHAR(50) NOT NULL
      , CONSTRAINT PK_Records PRIMARY KEY NONCLUSTERED (ID)
) ON [PRIMARY]
;

-- Populate the parent table
INSERT INTO DB.dbo.Records (Label)
VALUES
      ('Record 1')
      , ('Record 2')
      , ('Record 3')
;

-- Display the population of the parent table
SELECT *
FROM DB.dbo.Records
;

-- If the child table exists, drop it
IF OBJECT_ID('DB.dbo.Notes') IS NOT NULL
      DROP TABLE DB.dbo.Notes
;

-- Create the child table
CREATE TABLE DB.dbo.Notes (
      ID INTEGER IDENTITY(1,1) NOT NULL
      , RecordID INTEGER NOT NULL
      , Sequence INTEGER NOT NULL
      , Note VARCHAR(50) NOT NULL
      , CONSTRAINT PK_Notes PRIMARY KEY NONCLUSTERED (ID)
) ON [PRIMARY]
;

-- Add the foreign key to the child table
ALTER TABLE DB.dbo.Notes
ADD CONSTRAINT FK_Notes_Records
      FOREIGN KEY(RecordID)
      REFERENCES DB.dbo.Records(ID)
;

-- Populate the child table
INSERT INTO DB.dbo.Notes (RecordID, Sequence, Note)
VALUES
      (1, 1, 'First record first note')
      , (1, 2, 'First record second note')
      , (1, 3, 'First record third note')
      , (2, 1, 'Second record first note')
      , (2, 2, 'Second record second note')
      , (3, 1, 'Third record first note')
;

-- Display the population of the child table
SELECT *
FROM DB.dbo.Notes
;

Solution: We will create the query to join the Notes to the Record to meet the users requirement. Note the use of FOR XML PATH to concatenate the notes into a single string, in this case with a semicolon delimiter. The CROSS APPLY join allows us to group, order, and join the notes for each parent record.

-- For each parent, display child records in a single column
SELECT
      Records.ID
      , Records.Label
      , dTbl.Notes
FROM
      DB.dbo.Records
      CROSS APPLY (
            SELECT Notes.Note + '; ' AS [text()]
            FROM DB.dbo.Notes
            WHERE Notes.RecordID = Records.ID
            ORDER BY Notes.Sequence
            FOR XML PATH('')
      ) AS dTbl (Notes)
;

Advertisements
Post a comment or leave a trackback: Trackback URL.

Trackbacks

  • […] a previous post we demonstrated how to use the FOR XML clause to concatenate multiple rows of one data field into a […]

  • By Range Notation | T-SQL Ref on 07 May 2015 at 1300

    […] a single, comma-separated value, we use the FOR XML clause. (Haven’t done that before? Check here for more explanation, or here for another variation.) This entire process is demonstrated in the […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: