Link to home
Start Free TrialLog in
Avatar of onebite2
onebite2

asked on

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

Avatar of Faiga Diegel
Faiga Diegel
Flag of United States of America image

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

Avatar of onebite2
onebite2

ASKER

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.
Faiga16,
Instead it is retrieving all the dates instead of one particular date....
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

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

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....
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?


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

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

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!
What version of sql server are you using?
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

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 )
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!
Can you post the script to create the tables you are querying?
Avatar of Anthony Perkins
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
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

ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial