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!
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
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.
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.
ASKER
Faiga16,
Instead it is retrieving all the dates instead of one particular date....
Instead it is retrieving all the dates instead of one particular date....
Does this get you pointed in the right direction?
Matt
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 )
Or this? (added orders table to get date)
sorry I haven't tested this but hopefully it helps,
Matt
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 )
ASKER
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....
"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?
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 )
Corrected one problem. Try this.
Matt
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 )
ASKER
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!
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?
ASKER
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:
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
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.Date Started, 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 )
Results : 01/21/2008 1 computer,3 CPU,2 laptop
SELECT DISTINCT
Convert(Varchar(15),c.Date
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 )
ASKER
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!
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?
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
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
ASKER
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!
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
FROM Orders a
INNER JOIN OrdersUserIDs b ON a.OrderID = b.OrderID
WHERE StoreID= 'system'
GROUP BY Convert(Varchar(15),a.Date
order by a.DateStarted