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

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: PeopleSoft Tutorials, SQL

Subscribe to Comments RSS Feed in this post

7 Responses

Raja #

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.

Rakesh Subhagan #

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.

Dinesh kumar #

Hi Rakesh,

Very informative,Thanks for Sharing……

- Rakesh Subhagan #
  
  Hi Dinesh,
  
  Glad to know that it was informative.
  The credit goes to Raja for publishing this.
  
  - Raja #
    
    Thanks Dinesh and Rakesh

Chethan Dhanaraj #

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