需要有关将相邻行组合成单行的SQL查询的帮助

I need a solution to a problem that has the table structure as listed below. Input

我需要一个具有下面列出的表结构的问题的解决方案。输入

1 1/1/2009 Product1
2 2/2/2009 Product2
3 3/3/2009 Product3
4 4/4/2009 Product4
5 5/5/2009 Product5

1 1/1/2009 Product1 2 2/2/2009 Product2 3 3/3/2009 Product3 4 4/4/2009 Product4 5 5/5/2009 Product5

Output

1 1/1/2009 2/2009 Product1
2 3/3/2009 4/4/2009 Product3
3 5/5/2009            Product5

1 1/1/2009 2/2009 Product1 2 3/3/2009 4/4/2009 Product3 3 5/5/2009 Product5

I tried using CTE. But was not very sucessful in extracting the second row value. Appreciate any help. Thanks.

我尝试过使用CTE。但是在提取第二行值时并不是非常成功。感谢任何帮助。谢谢。

2 个解决方案

#1

I don't know whether Russ' answer helps you at all. Here is a link to an article that explains how to add row numbers to the results of a query. (Search for "row_number" to find the most likely example.)

我不知道Russ的回答是否对你有帮助。以下是一篇文章的链接,该文章解释了如何将行号添加到查询结果中。 (搜索“row_number”以找到最可能的示例。)

Once you have a query numbering the rows properly, you should be able to throw that into a CTE, then select from it twice -- once for odd numbers, then again for even numbers. Have each result return the even numbered value for joining (odd - 1 = even). At that point, you can join the results of the queries and get two products on one row.

一旦你有一个正确的行编号查询,你应该能够将它扔进CTE,然后从中选择两次 - 一次是奇数,再一次是偶数。每个结果都返回偶数编号的值(奇数 - 1 =偶数)。此时,您可以加入查询结果并在一行中获得两个产品。

#2

You are looking for PIVOT: http://msdn.microsoft.com/en-us/library/ms177410.aspx
Here is a best shot with the info you gave, I do something similar in one of my apps. You may need to use a dynamic SQL query if the pivot values change.

你正在寻找PIVOT:http://msdn.microsoft.com/en-us/library/ms177410.aspx这是你给我的信息的最佳镜头,我在我的一个应用程序中做了类似的事情。如果透视值更改,您可能需要使用动态SQL查询。

SELECT *
    FROM (SELECT [Date]
        ,[Product] 
      FROM [Values]
   PIVOT (Max([Date])
          FOR [Product]
           IN ('put date ranges here')) pvt

here is what mine looks like, this will allow for a set of different values. This is used in a form builder to retrive the values of user input

这是我的样子,这将允许一组不同的价值观。这在表单构建器中用于检索用户输入的值

--//Get a comma delimited list of field names from the field table for this form
DECLARE @FieldNames varchar(max)
SELECT @FieldNames = COALESCE(@FieldNames + ', ', '') + '[' + CAST([FieldName] AS varchar(max)) + ']'
  FROM [Fields]
 WHERE [FormID] = @FormID

--//create a dynamic sql pivot table that will bring our 
--//fields and values together to look like a real sql table
DECLARE @SQL varchar(max)
SET @SQL = 
 'SELECT *
    FROM (SELECT [Values].[RowID]
            ,[Fields].[FieldName]
            ,[Values].[Value] 
            FROM [Values]
      INNER JOIN [Fields] ON [Fields].[FieldID] = [Values].[FieldID]
           WHERE [Fields].[FormID] = ''' + @FormID + ''') p
   PIVOT (Max([Value])
      FOR [FieldName]
      IN (' + @FieldNames + ')) pvt'

--//execute our sql to return the data
EXEC(@SQL)