TSQL Transpose a column of a table into a row

user_2652715 0
Viewed 61

I have a temporary table with one column containing 3 pieces of data: Code, Date, and Quantity.

Example: 

-------
FR123456
24/02/1988
500

I need to extract the data in this column into separate columns.

Example:

Code     | Date       | Quantity
---------  -----------  ---- 
FR123456 | 24/02/1988 | 500

I used this code:

SELECT [1], [2], [3]
FROM  
(
    SELECT row_number() OVER (ORDER BY splitdata DESC) AS Id, splitdata  
    FROM splitdata 
) AS SourceTable  
PIVOT  
(  
    MIN (splitdata)
    FOR id IN ([1], [2], [3])  
) AS PivotTable;

The problem with it is that once the content of data changes, I may get the quantity content into the date column due to the aggregate function (MIN).

tsql c#
1 Answers

I assume this is SQL-Server related...

Your problem is: A SQL-Server table does not have any kind of implicit sort order. A simple SELECT * FROM SomeWhere can return in the sort order you've inserted your data, but can return completely different as well. The only chance to ensure a sort order is an ORDER BY at the outer-most query against a (set of) unique column(s).

You can create a sort order by kind of analysing your data:

This is a mockup-table with your test data:

DECLARE @mockup TABLE(YourColumn VARCHAR(100));
INSERT INTO @mockup VALUES
  ('FR123456')
 ,('24/02/1988')
 ,('500');

--The query will check the values if they can be casted to a number, to a date or not.
--This will be used to place a sort order to the values.
--I use 105 in CONVERT to enforce the dateformat dd-MM-yyyy

 SELECT CASE WHEN TRY_CONVERT(DATE,YourColumn,105) IS NOT NULL THEN 2
             ELSE CASE WHEN TRY_CAST(YourColumn AS INT) IS NOT NULL THEN 3 ELSE 1 
                  END 
        END AS SortOrder
        ,YourColumn
 FROM @mockup
 ORDER BY SortOrder;

But if there are several triplets in your table, not just one as in your sample, I'm afraid you're lost...

Btw: Your own approach tries to do exactly the same:

SELECT row_number() OVER (order by splitdata desc)as Id

This will create kind of a sort order number, but it will be random (a quantity will appeare before or after the date depending on alphanumerical rules).

Hint

Add an IDENTITY column to your table. This will use an increasing number for any row the moment it is created. These values can be used to enforce the order as inserted (by using ORDER BY with this column).

UPDATE: Your query

 SELECT [1], [2] , [3]
    FROM  
    (SELECT CASE WHEN TRY_CONVERT(DATE,splitdata,105) IS NOT NULL THEN 2
             ELSE CASE WHEN TRY_CAST(splitdata AS INT) IS NOT NULL THEN 3 ELSE 1 
                  END 
        END AS Id , splitdata  
    from @mockup ) AS SourceTable  
    PIVOT  
    (  
    MIN (splitdata)
    FOR id IN ([1], [2], [3])  
    ) AS PivotTable;
user_50892040
Related