locked
Sql query RRS feed

  • 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

     

     

     

    Monday, November 3, 2008 8:18 AM

Answers

  •  

    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

     

     

    Monday, November 3, 2008 9:57 AM

All replies

  •  

    There are how many Invoice values per day?

    Per day the number of Invalues are fixed?

    Monday, November 3, 2008 8:26 AM

  •              
                
                
                
    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 
    Monday, November 3, 2008 8:43 AM
  • 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

    Monday, November 3, 2008 9:49 AM
  •  

    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

     

     

    Monday, November 3, 2008 9:57 AM