November 14, 2006 at 5:58 pm
Hi all, I am trying to create a flat file for a sap upload. The problem is this:
The flat file format has say 10 fields in it. The first 5 of those fields from the table will all be the same (for this query) and the flat file requires that the first row has all fields filled in, but subsequent rows have the first 5 blank.
I am trying to work out how to do this. I thought of some type of CASE using the rownum (old Oracle person) byt it seems that SQL server does not havea rownum.
I thought of a TOP to get the first row, unioned with the rest, but in the second part of the union I need to get rid of the first row to avoid duplicate entries.
Am a bit lost and help would be appreciated.
thanks
Andrew
November 14, 2006 at 9:45 pm
Andrew
I may have completely misunderstood your issue still here are my thoughts
"I thought of a TOP to get the first row, unioned with the rest, but in the second part of the union I need to get rid of the first row to avoid duplicate entries. "
did u use UNION ALL or only UNION? I think union will work here becoz it removes duplicate rows (first row in this case).
Ex:- Products table (productid INT)
productid
-------- 1 2 3
select top one productid from products
union
select productid from products -- will return only 3 records. BUT
select top one productid from products
union all
select productid from products -- will return 4 records. productid 1 will be returned twice
"Keep Trying"
November 17, 2006 at 7:56 am
If you have SQL Server 2005 you can use the RANK() FUNCTION
SELECT TOP 1 RANK() OVER (ORDER BY COL1,COL2,COL2,COL3,COL4,COL5) AS RANK
,COL1,COL2,COL3,COL4,COL5
UNION ALL
SELECT RANK() OVER (ORDER BY COL1,COL2,COL2,COL3,COL4,COL5) AS RANK
,NULL,NULL,NULL,COL4,COL5
and then delete the row that has rank=1 and col1 as null and delete the rank column.
Hope this helps
Allan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply