sql server programming: Hi, I have a select that give me this result:
EJ.

SELECT Codigo + ' ' + 'CANT:' + ' ' + CAST(SUM(Quantity) AS VARCHAR)+
CHAR(9) + 'PEDIMENTO:' + ' ' + Pedimento
+ ' ' +
'FECHA:' + CAST((SELECT DAY(FIA)) AS VARCHAR) + '/' + CAST((SELECT
MONTH(FIA)) AS VARCHAR) + '/' + CAST((SELECT YEAR(FIA)) AS VARCHAR) AS
Pedimento
FROM VW_FACPED
WHERE Factura = 9--$[$38.44.0] AND Codigo = $[$38.1.0]
GROUP BY Pedimento, Codigo, FIA, Factura, BaseType
ORDER BY Codigo ASC


Result:

KRI3019 CANT: 45 PEDIMENTO: 6739747890273 187 - Nuevo Laredo FECHA:15/5/2004
SAV6417 CANT: 150 PEDIMENTO: 123456789 169-dfgdfgdfgdfgdfgfdgdf
FECHA:30/9/2004
SAV6417 CANT: 250 PEDIMENTO: 123456789 150-sdfgdfgdfgh FECHA:30/9/2004
SAV6417 CANT: 250 PEDIMENTO: 123456789 OK hsfsjkdfhsdjkhfjk FECHA:30/9/2004


Until Now everythin ig OK!, but every line is one row... I need to put the
result into two text fields, each fields should alternate every row li this:

FIELD 1:
KRI3019 CANT: 45 PEDIMENTO: 6739747890273 187 - Nuevo Laredo FECHA:15/5/2004
FIELD 2:
SAV6417 CANT: 150 PEDIMENTO: 123456789 169-dfgdfgdfgdfgdfgfdgdf
FECHA:30/9/2004
FIELD 1:
SAV6417 CANT: 250 PEDIMENTO: 123456789 150-sdfgdfgdfgh FECHA:30/9/2004
FIELD 2:
SAV6417 CANT: 250 PEDIMENTO: 123456789 OK hsfsjkdfhsdjkhfjk FECHA:30/9/2004

etc...

I hope you can help me..

Thanks in advanced!

Sergio

Sergio,

Since your query has ORDER BY Codigo ASC, the order of your results is
not completely defined. There is no way to know whether you will get

KRI3019 CANT: 45 PEDIMENTO: 6739747890273 187 - Nuevo Laredo FECHA:15/5/2004
SAV6417 CANT: 150 PEDIMENTO: 123456789 169-dfgdfgdfgdfgdfgfdgdf FECHA:30/9/2004
SAV6417 CANT: 250 PEDIMENTO: 123456789 150-sdfgdfgdfgh FECHA:30/9/2004
SAV6417 CANT: 250 PEDIMENTO: 123456789 OK hsfsjkdfhsdjkhfjk FECHA:30/9/2004

or

KRI3019 CANT: 45 PEDIMENTO: 6739747890273 187 - Nuevo Laredo FECHA:15/5/2004
SAV6417 CANT: 250 PEDIMENTO: 123456789 150-sdfgdfgdfgh FECHA:30/9/2004
SAV6417 CANT: 150 PEDIMENTO: 123456789 169-dfgdfgdfgdfgdfgfdgdf FECHA:30/9/2004
SAV6417 CANT: 250 PEDIMENTO: 123456789 OK hsfsjkdfhsdjkhfjk FECHA:30/9/2004

or

KRI3019 CANT: 45 PEDIMENTO: 6739747890273 187 - Nuevo Laredo FECHA:15/5/2004
SAV6417 CANT: 250 PEDIMENTO: 123456789 OK hsfsjkdfhsdjkhfjk FECHA:30/9/2004
SAV6417 CANT: 250 PEDIMENTO: 123456789 150-sdfgdfgdfgh FECHA:30/9/2004
SAV6417 CANT: 150 PEDIMENTO: 123456789 169-dfgdfgdfgdfgdfgfdgdf FECHA:30/9/2004


or another order. And then which pairs of rows do you want in Field1
and Field2 of the same row? To be precise about the result you want,
you must have enough columns in the ORDER BY clause to determine the
ordering exactly.

If you do, it's messy, but possible. (A report writer might have better
options than SQL.). It will look something like this. Here I'm
assuming that Col1, Col2, and Col3 are the columns you need to order by,
and Col1, Col2, and Col3 have column types are varchar(20), varchar(10),
and int.

create view V as
select
Codigo + ... as Pedimento,
cast(Col1 as char(20)) + cast(Col2 as char(10)) + str(Col3,12) as OrderCol
from ...
group by
Codigo, ...,
cast(Col1 as char(20)) + cast(Col2 as char(10)) + str(Col3,12)
go

select
max(case when whichField = 0 then Pedimento end) as Field1,
max(case when whichField = 1 then Pedimento end) as Field2
from (
select (count(V2.Pedimento)-1)/2 as whichRow,
(count(V2.Pedimento)-1)%2 as whichField, V1.Pedimento
from V as V1 join V as V2
on V2.OrderCol <= V1.OrderCol
group by V1.OrderCol, V1.Pedimento
) G
group by whichRow
order by whichRow


Steve Kass
Drew University

[quoted text, click to view]