Concatenating Rows in SQL Server

We had a requirement last month asking me to concatenate rows in SQL Server; I thought it would be a simple one as we do in an Excel sheet. But it turned out to be a nightmare tolling 8 hours of time. The below picture would explain more about the issue.

The actual SQL involved the tables like Bill accounting line, Billing header, Billing line and Billing header note. However, for convenience I will explain the situation with just two tables – BI_HDR and BI_HDR_NOTE.

SELECT DISTINCT E.BUSINESS_UNIT , E.BILL_TO_CUST_ID ,
E.INVOICE, J.TEXT254
FROM PS_BI_HDR E,PS_BI_HDR_NOTE J
WHERE E.BUSINESS_UNIT = J.BUSINESS_UNIT
AND E.INVOICE = J.INVOICE
AND J.STD_NOTE_FLAG <> 'Y'
AND E.BUSINESS_UNIT = 'xxxxx'
AND E.INVOICE = 'xxxxxx'
Business Unit Invoice Descr Amount Currency Header Note Description
USA01 1 inv1bu-usa 856.5 USD Note1
USA01 1 inv1bu-usa 856.5 USD Note2
USA01 1 inv1bu-usa 856.5 USD Note3
USA01 2 inv2bu-usa 100.1 USD Note1
USA01 2 inv2bu-usa 100.1 USD Note2
IND01 8 inv8bu-ind 56.6 INR Note1
IND01 8 inv8bu-ind 56.6 INR Note2
IND01 8 inv8bu-ind 56.6 INR Note3

Did you notice that the rows are almost the same except for the Header Note description that varies for all the invoices? My Requirement was to concatenate these similar rows. The table below will give a clear picture of it.

Business Unit Invoice Descr Amount Currency Header Note Description
USA01 1 inv1bu-usa 856.5 USD Note1, Note2, Note3
USA01 2 inv2bu-usa 100.1 USD Note1, Note2
IND01 8 inv8bu-ind 56.6 INR Note1, Note2, Note3

This result was obtained by including some SQL Server keyword and functions. This is the tweaked SQL that did the trick.

SELECT DISTINCT E.BUSINESS_UNIT , E.BILL_TO_CUST_ID , E.INVOICE,
STUFF(( SELECT ', ' + Z.TEXT254 FROM PS_BI_HDR_NOTE Z
WHERE Z.BUSINESS_UNIT = E.BUSINESS_UNIT
AND Z.INVOICE = E.INVOICE
AND Z.STD_NOTE_FLAG = 'N'
ORDER BY Z.NOTES_SEQ_NUM FOR XML PATH(''), TYPE ).VALUE('.','varchar(max)') ,1,2, '' ) AS "Header Note"
FROM PS_BI_HDR E,PS_BI_HDR_NOTE J
WHERE E.BUSINESS_UNIT = J.BUSINESS_UNIT
AND E.INVOICE = J.INVOICE
AND J.STD_NOTE_FLAG <> 'Y'
AND E.BUSINESS_UNIT = 'xxxxx'
AND E.INVOICE = 'xxxxxx'

If you are not familiar with Billing, the above tables may not make sense to you. No worries, I can also explain this with another set of tables as well.
Let us consider PS_EX_SHEET_HDR. This table has Employee IDs and Expense Sheet IDs as shown below. If you remember, my requirement was to concatenate similar rows as shown in the second image below.

Expense Sheet Header

Expense Sheet Header

Concatenated Result

Concatenated Result

To achieve this, you need to execute the below SQL.

SELECT A.EMPLID,STUFF((SELECT ', ' + B.SHEET_ID
FROM PS_EX_SHEET_HDR B
WHERE A.EMPLID=B.EMPLID
ORDER BY B.EMPLID
FOR XML PATH(''),TYPE ).VALUE('.','varchar(max)') ,1,2, '' )
AS SHEET_ID FROM PS_EX_SHEET_HDR A WHERE A.EMPLID IN ('107026','133244')
GROUP BY A.EMPLID

The above scenarios are for a MS SQL Server; for Oracle you may want to use the LISTAGG which is an aggregate function. Here’s an example.

SELECT A.BUSINESS_UNIT, A.INVOICE, LISTAGG(  B.TEXT254, '; ') 
WITHIN GROUP (ORDER BY  B.TEXT254) AS TEXT
  FROM PS_BI_HDR A, PS_BI_HDR_NOTE B
  WHERE ( A.BUSINESS_UNIT = B.BUSINESS_UNIT
     AND A.INVOICE = B.INVOICE
     AND A.INVOICE = '00647-00010001' )
  GROUP BY  A.BUSINESS_UNIT,  A.INVOICE;
Tags: ,
Subscribe to Comments RSS Feed in this post
6 Responses
  1. when trying to built a view with the above row concatenation SQL, use back end to create the view.., i.e., run the build script from back end. Because PeopleSoft errors out the “.value” while building the view. It also converts automatically the “.value” word into capital letters.

  2. Hi Raja,

    This came in really handy today. I wanted to list all BUs against PPLs and this was my method of choice to do that. Thanks for sharing this.

    SELECT A.CLASSID ,STUFF((SELECT ', ' + B.BUSINESS_UNIT
    FROM PS_SEC_BU_CLS B
    WHERE A.CLASSID=B.OPRCLASS
    ORDER BY B.OPRCLASS
    FOR XML PATH(''),TYPE ).VALUE('.','varchar(max)') ,1,2, '' )
    AS CLASSID FROM PSCLASSDEFN A   WHERE CLASSID LIKE 'PR%' 
    GROUP BY A.CLASSID

    BTW, you can use the %NoUppercase meta-SQL to prevent capitalization by the system.

  3. Hi Rakesh,

    Very informative,Thanks for Sharing……

    • Hi Dinesh,

      Glad to know that it was informative.
      The credit goes to Raja for publishing this.

  4. Great Article Raja. Recently used it in one of my requirements.

    I faced an issue while using this. The below SQL works fine when we execute in the database directly. But when we use it in a SQL view/ Application Engine SQL. we get an error saying – “VALUE” is not a valid function, property, or field.

    This is because PeopleSoft automatically converts the SQL we enter into uppercase and hence the line – ‘FOR XML PATH(”),TYPE ).value(‘.’,’varchar(max)’) ,1,2, ” )’ creates an issue.

    The solution for this is to use %NoUpperCase at the begining of the SQL
    Ex –

    %NoUpperCase 
     SELECT A.HRS_JOB_OPENING_ID 
     , STUFF(( 
     SELECT ', ' + B.EMPLID 
      FROM PS_HRS_JO_RPL B 
     WHERE A.HRS_JOB_OPENING_ID=B.HRS_JOB_OPENING_ID 
      ORDER BY B.HRS_JOB_OPENING_ID 
    FOR XML PATH(''),TYPE ).VALUE('.','varchar(max)') ,1,2, '' ) 
      FROM PS_HRS_JO_RPL A 
      GROUP BY A.HRS_JOB_OPENING_ID

    Regards,
    Chethan
    chethan.dhanaraj@gmail.com

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Read previous post:
PeopleSoft Journal Uploader
PeopleSoft Journal Import Error – User-defined type not defined

Close