Selecting values from table based on the date

Hi All,

My table contains UserName, Date, Count.

UserName Date Count

Test 01-08-2011 1
Test 01-08-2011 2
Test 02-08-2011 1
Test 03-08-2011 5

I need to select the values based on the date with group by UserName and date.
Eg:

UserName 1 2 3 .....31

Test 3 1 5 ......

Here 1,2,3 .... indicates date.
2 1 1 ...... indicates count

Can anyone tell me the query for this.

Thanks in advance.
Rajasri.

hi Rajasri,
Welcome to forums.
On the Date column do you have dates of only one month? or it spans across multiple months/years?

I added some code; but it may change based on your answer:

Code:

-- Create Table
create table Mytable (
UserName varchar(20),
MyDate date,
MyCount int
)

GO

-- Insert test data
declare @Seed varchar(100) ='this is a test example it contains some data I need to select the values based on the date with group by UserName and date'
insert into Mytable values(SUBSTRING(@seed, CONVERT(int, RAND()*10000)%50 +1, CONVERT(int, RAND()*10000)%20 +1), DATEADD(day, CONVERT(int, RAND()*300), '01/01/2011'), CONVERT(int, RAND()*300)%10+1)
GO 10000

--Declare input values
declare @name varchar(20), @DateList varchar(20)
set @name = ' contains '
Set @DateList = '1,3,4,5,6,7'

-- Convert the comma separated value to table variable
declare @RetTable table (DayNumber int)
    DECLARE @FirstPos int, @EndPos int, @Len int

    SELECT @Len = Len(@DateList), @FirstPos = 0

    WHILE @FirstPos < @Len
    BEGIN
        SELECT @EndPos = charindex(',', @DateList, @FirstPos + 1)
        IF @EndPos = 0 SELECT @EndPos = @Len + 1
        INSERT INTO @RetTable (DayNumber) VALUES (convert(int, LTrim(SUBSTRING(@DateList, @FirstPos + 1, @EndPos - @FirstPos - 1))))
        SELECT @FirstPos = @EndPos
    END
-- Query the data
select @name, r.DayNumber, SUM(a.MyCount)
FROM Mytable a inner join @RetTable r
ON DAY(a.MyDate) = r.DayNumber
AND a.UserName =@name
GROUP BY r.DayNumber