Want to convert columns data into rows...

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

SQL Server 2005 Forums

SQL Server Administration (2005)

Want to convert columns data into rows...

Author

Topic

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-04-03 : 03:18:56

Hello All,

I have one table with 9 different columns for e-mail address like mail_address1, mail_address2 etc....

now i want to insert serial no 1 for mail_address1, serial no 2 for mail_address2 like wise for a particular id.

Current Format :-

code mail_address1 mail_address2 mail_address3
1111 abc@yahoo.com xyz@yahoo.com null

Required format:-
code sr_no mail_address
1111 1 abc@yahoo.com
1111 2 xyz@yahoo.com
1111 3 null

i hope that i am clear with my question.

Can any one help me?

Thanks
Prashant

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-03 : 03:58:31

Use the UNPIVOT operator available in SQL Server 2005.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-03 : 04:04:29

[code]DECLARE @Sample TABLE (Code INT, Mail1 VARCHAR(200), Mail2 VARCHAR(200), Mail3 VARCHAR(200))

INSERT @Sample
SELECT 1111, 'abc@yahoo.com', 'xyz@yahoo.com', NULL

SELECT p.Code,
CAST(RIGHT(p.Col, 1) AS TINYINT) AS sr_no,
NULLIF(p.Mail, CHAR(7)) AS Mail
FROM (
SELECT Code,
COALESCE(Mail1, CHAR(7)) AS Mail1,
COALESCE(Mail2, CHAR(7)) AS Mail2,
COALESCE(Mail3, CHAR(7)) AS Mail3
FROM @Sample
) AS s
UNPIVOT (
Mail
FOR Col IN (s.Mail1, s.Mail2, s.Mail3)
) AS p
ORDER BY p.Code,
p.Col[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-03 : 04:05:55

Or this

SELECT		Code,
		sr_no,
		Mail
FROM		(
			SELECT	Code,
				1 AS sr_no,
				Mail1 AS Mail
			FROM	@Sample

			UNION ALL

			SELECT	Code,
				2,
				Mail2
			FROM	@Sample

			UNION ALL

			SELECT	Code,
				3,
				Mail3
			FROM	@Sample
		) AS d
ORDER BY	Code,
		sr_no

E 12°55'05.25"
N 56°04'39.16"

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-04-03 : 04:56:58

Yep, I got the required results.

Thanks you very much peso.

Regards,
Prashant Hirani

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources