Sql query

Question

HI

 

I want to get many records into one row.Now I have a report like below

InvoiceValue             Date

32369            03/11/2008

56985            03/11/2008

78521            03/11/2008

 

I want to generate report like below

 

Date           INValue1     INValue2    INValue3       INValue4

03/11/208    3216654   743236     4563211           896223

 

Invoice Value is not predefined value

 

Thanks

 

 

 

Answer 1

 

There are how many Invoice values per day?

Per day the number of Invalues are fixed?

Answer 2

             

            

            

            

Create Table #data 

(

InvoiceValue  int

,DateV        Datetime

            

)

 

Insert Into #data Values('32369','03/11/2008')

Insert Into #data Values('56985','03/11/2008')

Insert Into #data Values('78521','03/11/2008')

SELECT  

Datev

,[1] as InvoiceValue1

,[2] as InvoiceValue2

,[3] as InvoiceValue3

  FROM

  (

    SELECT 

Row_Number() Over (Order By InvoiceValue) as rowid

,Datev

,InvoiceValue

    FROM #data 

   ) PT

   

   PIVOT

    (

     max(InvoiceValue)

     FOR rowid IN

     ( [1], [2],[3])

) AS PVT

 

 drop table #data 

Answer 3

Hi

numebr of inovices are not same for each day,it chnange.I tried the sql query  which shipin anand wrote here,but it works properly,and give m value as below,but it not a report .

Thank you

 

Date                                 1      2      3         

2008-03-28 00:00:00.000 NULL NULL NULL 

2008-03-29 00:00:00.000 NULL NULL NULL

2008-03-30 00:00:00.000 NULL NULL NULL

2008-03-31 00:00:00.000 NULL NULL NULL

2008-04-01 00:00:00.000 NULL NULL NULL

Answer 4

 

If you don't know the number of Invalues per day, try this

 

Code Snippet

declare @Count int,@Cur int

declare @Qry nvarchar(4000)

 

set @Cur=1

 

select @Count=max(t.count) from (

select count(invoicevalue) count from TableName group by date)t

 

set @Qry=''

 

while @Cur<@Count+1

begin

set @Qry= @Qry + ',(select t.invoicevalue from (select ROW_NUMBER() over(order by invoicevalue) ROW_NUMBER, invoicevalue from TableName where date= t1.date) t where ROW_NUMBER='+ cast(@Cur as varchar(10)) + ') as Invalue' + cast(@Cur as varchar(10))

 

set @Cur=@Cur+1

 

end

 

set @Qry='select distinct t1.date ' + @Qry + ' from TableName t1'

 

 

exec sp_executesql @Qry