SQLserver2005 query

Here is a query attached in code snippet, output for the query  is now like this

DateStarted         fruits
   
1/21/2008           cpu
1/21/2008            computer
1/21/2008            laptop
1/21/2008           cpu
1/21/2008           laptop
1/21/2008           cpu


but I want the output to be as

1/21/2008     3cpu,2 laptop,1 computer

for that particular date all the products sold are in just one line,instead of displaying separately....

I need to attach this 2 columns Date,Products lo a grid using .NET .

I appreciate if any one can give me some work around for this.


Thanks!

SELECT Convert(Varchar(15),a.DateStarted, 101) DateStarted,b.product
FROM Orders a
INNER JOIN  OrdersUserIDs b ON a.OrderID = b.OrderID
WHERE StoreID= 'system'
order by a.DateStarted

Open in new window

onebite2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Faiga DiegelSr Database EngineerCommented:
SELECT Convert(Varchar(15),a.DateStarted, 101) DateStarted,b.product, count(b.product) as Count1
FROM Orders a
INNER JOIN  OrdersUserIDs b ON a.OrderID = b.OrderID
WHERE StoreID= 'system'
GROUP BY Convert(Varchar(15),a.DateStarted, 101) DateStarted,b.product
order by a.DateStarted

0
onebite2Author Commented:
Faiga16,

This query will definitely not give me the output I wanted....infact it is not retrieving any data at all.....


Can any one give me a work around for my question?



Thanks,
Cindy.
0
onebite2Author Commented:
Faiga16,
Instead it is retrieving all the dates instead of one particular date....
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

matty1stopCommented:
Does this get you pointed in the right direction?  

Matt
   SELECT DISTINCT 
	substring(products, 1, len(products)-1) as 'Products'
      FROM Orders a
     CROSS APPLY ( SELECT convert(varchar(8), count(*)) + ' '+ product + ',' 
                     FROM Orders b
                    WHERE a.OrderID = b.OrderID
			group by orderid
                    ORDER BY orderid 
                      FOR XML PATH('') )  D ( products )

Open in new window

0
matty1stopCommented:
Or this?  (added orders table to get date)

sorry I haven't tested this but hopefully it helps,

Matt
   SELECT DISTINCT 
    Convert(Varchar(15),a.DateStarted, 101) DateStarted,
	substring(products, 1, len(products)-1) as 'Products'
      FROM OrdersUserIDs a
join orders c
on a.orderid = c.orderid
     CROSS APPLY ( SELECT convert(varchar(8), count(*)) + ' '+ product + ',' 
                     FROM OrdersUserIDs b
                    WHERE a.OrderID = b.OrderID
			group by orderid
                    ORDER BY orderid 
                      FOR XML PATH('') )  D ( products )

Open in new window

0
onebite2Author Commented:
I tried executing the query which you posted but no luck it threw me this error:


"Column 'OrdersUserIDs.Product' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."


I appreciate if I can get some work around for this....
0
Faiga DiegelSr Database EngineerCommented:
It should give you something like;

DateStarted         fruits              Count1
1/21/2008           cpu                  3
1/21/2008            computer        1
1/21/2008            laptop              2

Does it not?


0
matty1stopCommented:
Try this

   SELECT DISTINCT 
    Convert(Varchar(15),a.DateStarted, 101) DateStarted,
	substring(products, 1, len(products)-1) as 'Products'
      FROM OrdersUserIDs a
join orders c
on a.orderid = c.orderid
     CROSS APPLY ( SELECT convert(varchar(8), count(*)) + ' '+ product + ',' 
                     FROM OrdersUserIDs b
                    WHERE a.OrderID = b.OrderID
			group by product
                    ORDER BY product 
                      FOR XML PATH('') )  D ( products )

Open in new window

0
matty1stopCommented:
Corrected one problem.  Try this.

Matt

   SELECT DISTINCT 
    Convert(Varchar(15),c.DateStarted, 101) DateStarted,
	substring(products, 1, len(products)-1) as 'Products'
      FROM OrdersUserIDs a
join orders c
on a.orderid = c.orderid
     CROSS APPLY ( SELECT convert(varchar(8), count(*)) + ' '+ product + ',' 
                     FROM OrdersUserIDs b
                    WHERE a.OrderID = b.OrderID
			group by product
                    ORDER BY product 
                      FOR XML PATH('') )  D ( products )

Open in new window

0
onebite2Author Commented:
I got the output as how I have earlier

1/21/2007   cpu
1/21/2007  laptop
1/21/2007   computer



But I want the output as this:

1/21/2007  1cpu,1laptop,1computer


Hope we get some work around!
0
matty1stopCommented:
What version of sql server are you using?
0
onebite2Author Commented:
I am using sqlserver2005....

I got the work around to fix this problem....I wrote a stored proc and it did give me the result to some extent but I need the count of the products

For the stored proc which I wrote my output is like :

1/21/2008   CPU,COMPUTER,LAPTOP

But I need the count too as 1 cpu,2 laptop.3 computer....

Can you find some workaround to fix this??

HERE GOES MY PROC:
CREATE PROCEDURE getProducts4Specificdate
(@DateStarted varchar(15))
AS
Begin
--      Declare @OrderDate as varchar(15)
        DECLARE @OrderList varchar(2000)
--      Set @OrderDate = '01/07/2008'
        select  @OrderList= coalesce( @OrderList + ',', '') + rtrim(b.Product)
        FROM Orders a
        INNER JOIN  OrdersUserIDs b ON a.OrderID = b.OrderID
        WHERE convert(varchar(15),a.DateStarted,101)= @DateStarted
        group by Convert(Varchar(15),a.DateStarted, 101), b.product
        order by Convert(Varchar(15),a.DateStarted, 101)
 
        SELECT @DateStarted AS DateStarted,
                @OrderList AS OrderList
 
End

Open in new window

0
matty1stopCommented:
You ran this query and it didn't work?  I've tested it using tables that fit your original query and got the results in the format you requested

Results : 01/21/2008      1 computer,3 CPU,2 laptop

   SELECT DISTINCT
    Convert(Varchar(15),c.DateStarted, 101) DateStarted,
      substring(products, 1, len(products)-1) as 'Products'
      FROM OrdersUserIDs a
join orders c
on a.orderid = c.orderid
     CROSS APPLY ( SELECT convert(varchar(8), count(*)) + ' '+ product + ','
                     FROM OrdersUserIDs b
                    WHERE a.OrderID = b.OrderID
                  group by product
                    ORDER BY product
                      FOR XML PATH('') )  D ( products )
0
onebite2Author Commented:
I ran the query which you posted but did not give the output which I wanted as

Results : 01/21/2008      1 computer,3 CPU,2 laptop

Instead it gave me the output as :

01/21/2008   1computer
01/21/2008    3cpu
01/21/2008    2 laptop


I want them all in one line just the dates and all products concatenated...


Hope you understood me!
0
matty1stopCommented:
Can you post the script to create the tables you are querying?
0
Anthony PerkinsCommented:
Your problem is quite simple:
1 Create a UDF as follows:

Alter Function udf_GetNames(@DateStarted datetime)

Returns varchar(200)

As

Begin

Declare @Names varchar(200)

Select      @Names = IsNull(@Names + ',', '') + CAST(COUNT(*) as varchar(10)) + Fruits
From      YourTableNameGoesHere
Where      DateStarted = @DateStarted
Group By
      Fruits

Return @Names
End


2. Execute it as follows:
Select DateStarted,
           dbo.udf_GetNames(Fruits) FruitListWithCount
From  YourTableNameGoesHere
Group By DateStarted
0
onebite2Author Commented:
Hey acperkins,

This is how I changed the query to as you advised but its throwing me some errors like:

Column 'Orders.DateStarted' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


Hope you can find me some work around for this.

Thanks!



CREATE Function udf_GetNames(@DateStarted datetime)
 
Returns varchar(200)
 
As
 
Begin
 
Declare @Names varchar(200)
 
Select      @Names = IsNull(@Names + ',', '') + CAST(COUNT(*) as varchar(10)) + b.Product
FROM        Orders a
INNER JOIN  OrdersUserIDs b ON a.OrderID = b.OrderID  
Where      a.DateStarted = @DateStarted
Group By
      b.Product
 
Return @Names
End
 
 
2. Execute it as follows:
Select a.DateStarted,dbo.udf_GetNames(b.Product) ProductListWithCount
FROM Orders a
INNER JOIN  OrdersUserIDs b ON a.OrderID = b.OrderID
Group By b.product

Open in new window

0
Anthony PerkinsCommented:
Sorry I mislead you.  It should be:

Select a.DateStarted,dbo.udf_GetNames(a.DateStarted) ProductListWithCount
FROM Orders a
INNER JOIN  OrdersUserIDs b ON a.OrderID = b.OrderID
Group By a.DateStarted
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.