SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Converting multiple recods into columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jim_jim
Constraint Violating Yak Guru

USA
298 Posts

Posted - 10/11/2013 :  15:56:03  Show Profile  Reply with Quote
Hi All
I have a table called DISTRIBUTION_SL in which there will multiple records for each request as shown below in the data.I would like to convert the recepient email into columns and then join with other tables so that the entire request information is seen in one row rathar than multiple rows


Table
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
CREATE TABLE [dbo].[DISTRIBUTION_SL](
[requestid] [numeric](18, 0) NOT NULL,
[rptdis] [char](1) NOT NULL,
[recipnm] [varchar](50) NULL,
[emailadr] [varchar](75) NULL,
[Seq] [int] NOT NULL,
[phone] [varchar](30) NULL,

CONSTRAINT [PK__REPORT_DISTRIBUTION] PRIMARY KEY CLUSTERED
(
[requestid] ASC,
[rptdis] ASC,
[Seq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Sample Data
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
313524 B Jennifer XYZ jpool@yahoo.com 4 111-111-1111
313524 C Lisa Yaris lisa.y@gmail.com 5 222-222-2222
313524 S Heather Matiz heather_m@hotmail.com 2 333-333-3333
313524 V Paul Sentra paul@aol.com 1 444-444-4444
313524 V Torin xavier elcl@abc.com 3 555-555-5555
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

outcome should be like below

313524 jpool@yahoo.com lisa.y@gmail.com heather_m@hotmail.com paul@aol.com elcl@abc.com


can someone please help?

thanks

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/12/2013 :  04:00:53  Show Profile  Reply with Quote

SELECT requestid,
STUFF((SELECT ' ' + emailadr FROM dbo.DISTRIBUTION_SL WHERE requestid = d.requestid FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT requestid FROM dbo.DISTRIBUTION_SL)d


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

USA
298 Posts

Posted - 10/14/2013 :  08:30:06  Show Profile  Reply with Quote
This is not how i want,I would like to see each email address as a separate column

Thanks
quote:
Originally posted by visakh16


SELECT requestid,
STUFF((SELECT ' ' + emailadr FROM dbo.DISTRIBUTION_SL WHERE requestid = d.requestid FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT requestid FROM dbo.DISTRIBUTION_SL)d


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/14/2013 :  08:59:01  Show Profile  Reply with Quote
Then you should have specified that or shown column headers.
Anyways here you go


SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY requestid ORDER BY rptdis) AS Seq,requestid,emailadr
FROM dbo.DISTRIBUTION_SL
)t
PIVOT(MAX(emailadr) FOR Seq IN ([1],[2],[3],[4],[5]))p


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/14/2013 :  09:03:07  Show Profile  Reply with Quote
to make it dynamic use

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

USA
298 Posts

Posted - 10/14/2013 :  11:05:49  Show Profile  Reply with Quote
Thank you Visakh and sorry for not being clear.
You are just awesome
quote:
Originally posted by visakh16

Then you should have specified that or shown column headers.
Anyways here you go


SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY requestid ORDER BY rptdis) AS Seq,requestid,emailadr
FROM dbo.DISTRIBUTION_SL
)t
PIVOT(MAX(emailadr) FOR Seq IN ([1],[2],[3],[4],[5]))p


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/14/2013 :  12:55:28  Show Profile  Reply with Quote
thats ok
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000