CODEWITHSUNDEEP
sqlsql-server
sunleo
sunleo

Reputation: 10943

SQL Server Rows to Columns using subquery

I tried to get rows into column using comma delimeted using this but how to achieve this using subquery, I achived that in oracle.

SQL Server :

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + email
FROM RDT_USER
SELECT @listStr

Oracle :

 SELECT RTRIM(XMLAGG(XMLELEMENT(E, EMAIL || ',')).EXTRACT('//text()'), ',') AS RECEIVERID 
    FROM (SELECT DISTINCT (EMAIL) AS EMAIL
          FROM RDT_USER
        )

OUTPUT Expected :

[email protected],[email protected],[email protected],[email protected]

Upvotes: 2

Views: 1268

Answers (1)

Ivan Golović
Ivan Golović

Reputation: 8832

You can use this:

DECLARE @listStr VARCHAR(MAX) =
    STUFF(
        (
        SELECT  DISTINCT ',' + email
        FROM    RDT_USER
        FOR XML PATH(''), TYPE
        ).value('.', 'VARCHAR(MAX)')
        ,1,1,'')

SELECT @listStr

If you just want to select without variables, this should work:

SELECT 
    STUFF(
        (
        SELECT  DISTINCT ',' + email
        FROM    RDT_USER
        FOR XML PATH(''), TYPE
        ).value('.', 'VARCHAR(MAX)')
        ,1,1,'')

Upvotes: 2

Related Questions