views:

391

answers:

2

Hi all,

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

Output

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.

+1  A: 

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.

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)
Russ Bradberry
Thanks for the pointer, Russ. I will try using the PIVOT operator.
Techspirit
I had to finally go with CTEs, since I already had a query which was only giving me half the results.Thanks.
Techspirit
A: 

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

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.

John Fisher
I have a query that uses CTE along with the row number generated as the first column. I cannot group any of the columns in the input table. The query returned only even numbered rows when I used the % 2 operator.
Techspirit
We'll probably need to see the query to see what's wrong. I would guess that it's rough to place this into the comments. For the comparison, you'll need (x%2 = 0) as well as (x%2 = 1).
John Fisher
Thanks for the ideas, John. This is the query I have been working on ;With ProdListCTE As ( Select Row_Number() Over(order by ProdTime asc) as row_id, ProdTime, Desc, From ProdListStaged ) Insert into ProdList (ProdTime1, ProdTime2, Desc) Select A.ProdTime, B.ProdTime, A.Desc, From ProdListCTE A Join ProdListCTE B On A.row_id = B.row_id - 1
Techspirit
I used two CTEs, one for the odd rows and one for the even rows and select using a left outer join. Thanks, John.
Techspirit