SQL数据将列转换为行值 [英] SQL data convert column to row values

问题描述

我有一个表，只有一列，只有100行左右只有名字.但是我需要连续显示3个名字.这样我将获得34行，每行3个名称.

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.

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

没有条件只需要将单列值转换为3列数据即可.但是唯一的事情是需要按主键值进行排序.

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

推荐答案

此解决方案非常通用.
它可以解决主键中的空白，并且非常容易更改查询中的列数.
不过，Manchanx解决方案应该更快.

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.

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

Disclaimer:以下内容适用于Sql-Server.我认为它也适用于Oracle，但我不能保证.

假设您的主键是一个从1开始并连续递增(1、2、3，...)的整数列:

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, ...):

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;

编辑:针对Oracle兼容性的调整，如JörgenAndersson所建议的那样

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

这篇关于SQL数据将列转换为行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！