hi

i have made a view and its out put will be the following

InvDt           ItmNm           Qty              Mrp
20/08/08   abc                   20              100
21/08/08   abc                   30              100


Now i want to the output like this

invDt                                        ItmNm               Qty        
  Mrp.
20/08/08,21/08/08               abc                     50            100

Is It possible in to the ms sql server 2000

if yes then reply me soon plz help me in this


with regards
keyur joshi

Hi
I'd prefer doing such things on the client or upgrade to SQL Server 2005
select convert(varchar(10),t1.InvDt,101)+',' +

convert(varchar(10),#t.InvDt,101),#t.ItmNm ,

t1.Qty+#t.Qty ,#t.Mrp from #t join #t t1

on #t.InvDt<t1.InvDt





Show quoteHide quote

Hi,

I would also recomend to use SQL2005 or application code, but I guess
sometimes we have to live with what we re given. so in case you need to do it
in SQL2000 you can use a cursor as below. You may need to modify the code
below.

-- Drop table #invsum
Select sum(qty) As total, itmnm, mrp, space(2000) as dates into #invsum from
inv group by itmnm, mrp

Declare @citmnm varchar(100), @imrp Int, @dInvDt datetime

DECLARE #invdates CURSOR
FOR Select distinct invdt, itmnm, mrp From inv

OPEN #invdates

FETCH NEXT FROM #invdates INTO @dInvDt, @citmnm, @imrp
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        update #invsum set dates = LTRIM(RTRIM(dates)) + case when dates = ''
THEN '' ELSE ',' END +
            convert(CHAR(8), @dInvDt, 3 )
            WHERE itmnm = @citmnm AND mrp = @imrp
    END
    FETCH NEXT FROM #invdates INTO @dInvDt, @citmnm, @imrp
END

CLOSE #invdates
DEALLOCATE #invdates
GO

SELECT * FROM #invsum

Best Regards,
Roshan Jayalath


Show quoteHide quote

Hi,

Regarding my last reply to you, please note that the code may not be optimal
as I did it quickly and you have to note that I have put 2000 character
limit. When you reach 2000 character, bang..... so you may try a text column.

Best Regards,
Roshan Jayalath

Show quoteHide quote

Migrating SQL 2000 to SQL 2005, any risk I change db collation too?
Trigger and Misc.
Adding memory has degraded performance
Alter Index On Database Rebuild (SQL Server 2005)
SQL Server 2005 Cannot refer column Schema Across Database
Bitwise in SQL Server
SQL Server 2005 SSMS List Role Permission
free text search in large starschemes
Update?
[SAN] More spindles via multiple RAID/LUNs, or fewer & more focused LUNs?