SQL data convert column to row values

Question

0.00/5 (No votes)

See more:

Hi,

I have a table with only one column about 100 rows of only names. But I need to display the 3 names in a row. So that I will get 34 rows each row with 3 names.

VB

Example:

Name
_____

Raj
Sam
Guru
Tej
Avin
Sami
Fanst

I need to display above data as

Name Name1 Name2
____ _____ ______
Raj  Sam    Guru
Tej  Avin   Sami
Fanst

No condition just need to covert single column value into 3 columns data. But only thing is need to order by primary key value.

Posted 17-Mar-15 18:27pm

tsudhirkumar

Add a Solution

2 solutions

Solution 1

~~Disclaimer: The following would work for Sql-Server. I assume it will work for Oracle too but I can't guarantee.~~

Assuming your primary key is a single integer column starting at 1 and incrementing continuously (1, 2, 3, ...):

SQL

SELECT t1.Name Name1,
       t2.Name Name2,
       t3.Name Name3
FROM TableName t1
LEFT JOIN TableName t2 ON t1.PK + 1 = t2.PK
LEFT JOIN TableName t3 ON t1.PK + 2 = t3.PK
WHERE Mod(t1.PK - 1, 3) = 0;

Edit: Adjustments for Oracle-compatibility, as suggested below by Jörgen Andersson

Posted 17-Mar-15 18:44pm

manchanx

Updated 18-Mar-15 2:23am

v2

Comments

Jörgen Andersson 18-Mar-15 8:19am

Drop the square brackets, they're SQL Server specific and not even necessary in most cases.
WHERE (t1.PK - 1) % 3 = 0; would be WHERE Mod(t1.PK - 1,3) = 0; in Oracle

[no name] 18-Mar-15 8:20am

Thank you!

Jörgen Andersson 18-Mar-15 8:24am

No problems

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)

Jörgen Andersson · Accepted Answer · 2015-03-18T02:16:00

This solution is quite generic.
It works with gaps in your primary key as well as being very easy to change the number of columns in the query.
Manchanx solution should be faster though.

SQL

WITH ordered AS (
    SELECT  name
           ,Row_Number() OVER (ORDER BY name) -1 AS rn --change the order by to your PK
    from    table1
    )
,modded as (    
    select  name
           ,Mod(rn,3) m    -- 3 is the number of columns
           ,Floor(rn/3) r  -- 3 is the number of columns

    from    ordered
    )
SELECT  *
FROM    modded
pivot   (
    Max(name)
    for m
    IN (0 as name,1 as name1,2 as name2)
    )
ORDER BY r