Converting multiple recods into columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting multiple recods into columns

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2013-10-11 : 15:56:03

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

52326 Posts

Posted - 2013-10-12 : 04:00:53

[code]
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
[/code]

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

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2013-10-14 : 08:30:06

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-14 : 08:59:01

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-14 : 09:03:07

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

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2013-10-14 : 11:05:49

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-14 : 12:55:28

thats ok
you're welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources