how to Convert columns into rows with same primary key id

Question

0.00/5 (No votes)

See more:

I have one record as:

id member_photo1 member_photo2 member_photo3
223 photo1.jpg photo2.jpg photo3.jpg

and i want to convert it as

id member_photo
223 photo1.jpg
223 photo2.jpg
223 photo3.jpg

How to do this in Sql server

Posted 17-Apr-13 21:19pm

prathamesh812

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-04-17T21:28:00

Solution1 (by Aarti) is very good. Another way is to use CTE[^].
Try this:

SQL

DECLARE @tbl TABLE (id INT, member_photo1 NVARCHAR(30), member_photo2 NVARCHAR(30), member_photo3 NVARCHAR(30))

INSERT INTO @tbl (id, member_photo1, member_photo2, member_photo3)
VALUES(223, 'photo1.jpg', 'photo2.jpg', 'photo3.jpg')
INSERT INTO @tbl (id, member_photo1, member_photo2, member_photo3)
VALUES(224, 'photo10.jpg', 'photo20.jpg', 'photo30.jpg')
INSERT INTO @tbl (id, member_photo1, member_photo2, member_photo3)
VALUES(225, 'photo100.jpg', 'photo200.jpg', 'photo300.jpg')
INSERT INTO @tbl (id, member_photo1, member_photo2, member_photo3)
VALUES(226, 'photo1000.jpg', 'photo2000.jpg', 'photo3000.jpg')

SELECT *
FROM @tbl

;WITH MyMembers AS
(
	SELECT id, member_photo1 AS PhotoName
	FROM @tbl
	UNION ALL
	SELECT id, member_photo2 AS PhotoName
	FROM @tbl
	UNION ALL
	SELECT id, member_photo3 AS PhotoName
	FROM @tbl
)
SELECT * 
FROM MyMembers
ORDER BY id

Result:

223	photo1.jpg
223	photo2.jpg
223	photo3.jpg
224	photo30.jpg
224	photo20.jpg
224	photo10.jpg
225	photo100.jpg
225	photo200.jpg
225	photo300.jpg
226	photo3000.jpg
226	photo2000.jpg
226	photo1000.jpg

Aarti Meswania · Answer 2 · 2013-04-17T21:23:00

Solution 1

SQL

select id, member_photo1 as member_photo 
from TblNm

Union all

select id, member_photo2 as member_photo 
from TblNm

union all

select id, member_photo3 as member_photo 
from TblNm

Happy Coding!
:)

Posted 17-Apr-13 21:23pm

Aarti Meswania

Comments

Maciej Los 18-Apr-13 3:31am

+5!

Aarti Meswania 18-Apr-13 3:32am

Thank you! :)

prathamesh812 18-Apr-13 3:51am

Thanx a lot it works

Aarti Meswania 18-Apr-13 4:20am

Welcome!
Glad to help you! :)