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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Anthony PerkinsConnect With a Mentor Commented:
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
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
onebite2Author Commented:
Faiga16,
Instead it is retrieving all the dates instead of one particular date....
0
 
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
All Courses

From novice to tech pro — start learning today.