0

Transposing one column to a row.

I've obtained a list of email addresses using the following query:

select 
    user_login.email_address
from 
    user.login with(nolock)

Example of results are as follows:

Email
--------------------
abc@helloworld.com    
no_email    
romeo@abc.com.au  
Juliet@abc.com.au

What I would like to do is run the query, transpose and get results as Text (with semi-colons in between results), so I can automatically paste the results into an email like below:

Desired result:

abc@helloworld.com; romeo@aboc.com.au; juliet@abc.com.au

I tried to understand the PIVOT and UNPIVOT functions, but I'm just lost in how I can insert semicolons in between results. Hope someone can help. Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kitana
  • 13
  • 2

2 Answers2

1

If you're using SQL Server

select top 1
EMAIL = 
 STUFF((    SELECT '; '+ a.email_address AS [text()]  
FROM email_address a 
FOR XML PATH('') 
), 1, 1,'') 
from email_address
beejm
  • 2,381
  • 1
  • 10
  • 19
  • Thanks. I'm using SQL Server 2012. – Kitana Mar 28 '17 at 04:45
  • Reworked the script and it's returning in the format I wanted but there obviously a limit to line. It's cutting off the results. There's approximately 100+ email that I need to transpose. Hope you can help. Thanks. – Kitana Mar 28 '17 at 05:04
  • You could partition it by parts, for instance, get a-f, g-m, n-q, r-z; – beejm Mar 28 '17 at 05:08
  • also; http://stackoverflow.com/questions/3113360/for-xml-length-limitation – beejm Mar 28 '17 at 05:09
  • Got it working. Used script to grid instead of text. Thanks Kuya. – Kitana Mar 28 '17 at 08:49
0

you can use STUFF https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql

https://sqlwhisper.wordpress.com/2013/03/24/stuff-and-for-xml-path-for-string-concatenation/

select distinct sno ,
STUFF((Select ','+Scity
from @Test T1
where T1.sno=T2.sno
FOR XML PATH('')),1,1,'') from @Test T2
RoMEoMusTDiE
  • 4,739
  • 1
  • 17
  • 26