SQL Server Rows to Columns using subquery

Question

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]

Kindly show a sample output which u want

praveen
– praveen

2013-03-06 10:06:30 +00:00
Commented Mar 6, 2013 at 10:06 — praveen
– praveen, Commented Mar 6, 2013 at 10:06
Thanks for reply just check question.

Jericho
– Jericho

2013-03-06 10:09:03 +00:00
Commented Mar 6, 2013 at 10:09 — Jericho
– Jericho, Commented Mar 6, 2013 at 10:09

Ivan Golović · Accepted Answer · 2013-03-06 10:27:05Z

2

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,'')

edited Mar 6, 2013 at 10:27

answered Mar 6, 2013 at 10:12

Ivan Golović

8,8223 gold badges27 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jericho Over a year ago

bu tmy question is want to achieve this using sub query.

Ivan Golović Over a year ago

This approach is usually used for such tasks when using SQL Server. I don't know why you need subquery because this gets the result that you need...

Jericho Over a year ago

you are right but am using query to execute in java environment to execute as query not as function or procedure to have declare key word and other variable

Collectives™ on Stack Overflow

SQL Server Rows to Columns using subquery

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related