将数据表放入2列

I have a table that contains photos. One photo and its description in each record. I want to create a temp table that has two photos/descriptions per record. So, I need to create a report that has photos displayed in two columns.

我有一张包含照片的桌子。每张照片中的一张照片及其描述。我想创建一个临时表,每个记录有两张照片/描述。因此,我需要创建一个报告,其中的照片显示在两列中。

This is what I have:

这就是我所拥有的:

 1  Photo1, Description1
 2  Photo2, Description2
 3  Photo3, Description3
 4  Photo4, Description4

Here is what I am expecting:

这是我所期待的:

 Photo1, Description1, Photo2, Description2
 Photo3, Description3, Photo4, Description4

How can I get there using a stored procedure in SQL Server 2012?

如何使用SQL Server 2012中的存储过程到达那里?

2 个解决方案

#1

You didn't provide a lot of details here but here is a possibility. The first cte adds a row number to every row so we can ensure we have a consistent numbering pattern. Then we have a cte to retrieve the odd numbers followed by another cte to get the even numbers. Then we just join them together.

你这里没有提供很多细节,但这里有可能。第一个cte为每一行添加一个行号,这样我们就可以确保我们有一致的编号模式。然后我们有一个cte来检索奇数,然后是另一个cte来获得偶数。然后我们就加入他们吧。

with NumberedPhotos as
(
    select Photo
        , Description
        , ROW_NUMBER() over(order by PhotoID) as RowNum
    from SomeTable
)
, OddNumbers as
(
    select Photo
        , Description
        , RowNum
    from NumberedPhotos
    where RowNum % 2 = 1
)
, EvenNumbers as
(
    select Photo
        , Description
        , RowNum
    from NumberedPhotos
    where RowNum % 2 = 0
)
select o.Photo
    , o.Description
    , e.Photo
    , e.Description
from OddNumbers o 
left join EvenNumbers e on o.RowNum = e.RowNum - 1

#2

You could use Modulo like @Sean Lange mentioned, but you will want to join back to your table something like this:

你可以像@Sean Lange一样使用Modulo,但你会想要加入到你的桌面,如下所示:

;WITH NumberedPhotos
AS (
    SELECT photo_name
        ,photo_desc
        ,ROW_NUMBER() OVER (
            ORDER BY Photo_ID
            ) AS RowNum
    FROM photo_info
    )
SELECT  
     t.photo_name
    ,t.photo_desc
    ,n.photo_name
    ,n.photo_desc

FROM NumberedPhotos n
LEFT JOIN NumberedPhotos AS t ON n.rownum = t.rownum + 1
WHERE n.rownum % 2 = 0;

SQL Fiddle Demo

SQL小提琴演示

#1

with NumberedPhotos as
(
    select Photo
        , Description
        , ROW_NUMBER() over(order by PhotoID) as RowNum
    from SomeTable
)
, OddNumbers as
(
    select Photo
        , Description
        , RowNum
    from NumberedPhotos
    where RowNum % 2 = 1
)
, EvenNumbers as
(
    select Photo
        , Description
        , RowNum
    from NumberedPhotos
    where RowNum % 2 = 0
)
select o.Photo
    , o.Description
    , e.Photo
    , e.Description
from OddNumbers o 
left join EvenNumbers e on o.RowNum = e.RowNum - 1

#2