SQL Server Query to Teradata : XML PATH

Database

SQL Server Query to Teradata : XML PATH

I am new to Teradata and working on conversion from SQL Server to Teradata. Is there any way, I can impliment below code in Teradata? Thanks.

SELECT DISTINCT row_date 
, starttime
, logid
, CoreSkillList
, site_name
, SiteName
, Replace((select Cast(split AS VARCHAR(50)) + '' as 'data()'
From #tmpResults b
where b.row_date = a.row_date
AND b.starttime = a.starttime
AND b.logid = a.logid
AND b.rsv_level = 0
ORDER BY split
for xml path('')), ' ', ',') SPLITS
INTO #tmpFinal
FROM (SELECT *
FROM #tmpResults
WHERE rsv_level = 0) a
ORDER BY 1
, 2
, 3
, 4
3 REPLIES

Re: SQL Server Query to Teradata : XML PATH

Any possibility?

Senior Apprentice

Re: SQL Server Query to Teradata : XML PATH

If your TD system supports XML (native or as addon) there's an XML aggregate function. And you better replace the Scalar Subquery with a join:

CREATE VOLATILE TABLE #tmpFinal AS (
SELECT a.row_date
, a.starttime
, a.logid
, a.CoreSkillList
, a.site_name
, a.SiteName
, b.SPLITS
FROM #tmpResults AS a
JOIN
(
SELECT
row_date
,starttime
,logid
,OREPLACE((XMLAGG(TRIM(Split) ORDER BY Split) (VARCHAR(10000))), ' ', ',') AS SPLITS
FROM #tmpResults
WHERE rsv_level = 0
GROUP BY 1,2,3
) b
ON b.row_date = a.row_date
AND b.starttime = a.starttime
AND b.logid = a.logid
WHERE rsv_level = 0
) WITH DATA ON COMMIT PRESERVE ROWS

Re: SQL Server Query to Teradata : XML PATH

Thanks Dieter. I was really looking for something like "XMLAGG". This helps.