-
Need Query Help
I am trying to write a SQL query that returns the number of products that each customer has and lists the products on one line.
For example I have a database that contains CustomerID and Product and obviously if the Customer has multiple products there are multiple rows in the database. So if CustomerID '123' has Products 'X', 'Y' and 'Z' I want a query that lists the following
CustomerID #_of_Products Products
123 3 X, Y, Z
How would I go about doing this? Thanks
-
-
Does this method work for SQL 8.0 which is what I am using?
I am getting the error message "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."
Thanks
-
--use test
--drop table TBClientData
create table TBClientData (customerid int, Products varchar(100))
insert into TBClientData select 1,'x'
insert into TBClientData select 1,'y'
insert into TBClientData select 2,'z'
insert into TBClientData select 2,'a'
insert into TBClientData select 2,'t'
insert into TBClientData select 2,'q'
insert into TBClientData select 1,'k'
insert into TBClientData select 3,'l'
go
Create FUNCTION fn_Concat (@col int )
returns varchar(1000)
as
begin
declare @x varchar(1000)
set @x=''
select @x=@x+','+products from TBClientData where customerID = @col
set @x=substring(@x,2,len(@x))
return (@x)
end
go
--query
select customerID, dbo.fn_Concat (customerID) as products from
TBClientData group by customerID
--results
customerID,products
1,x,y,k
2,z,a,t,q
3,l
-
That worked perfect! Thanks!
-
I am having problem to post the my question. I tried few times posting it. I am new user to this forum. I don't find my post on this site and didn't get any email so i am not sure anyone replied for my post Please help. I have urgent quetion. I am registerd user. Thanks in advance.
-
I can see only this posting. could you post again? or please post it here in this thread?
-
Thanks. Here is the thing
Need a help to write a query.
I have a table with three columns.
A,B,C
A = Identity column
B = Units
C = Datetime
The data in table looks like as follows
A B C
--------------------------------
1 2701 1-31-04 09:40am
2 2701 1-31-04 09:49am
3 2701 1-31-04 10:40am
4 2701 1-31-04 10:04am
5 2701 1-31-04 02:40pm
6 2701 1-31-04 03:35pm
7 2701 1-31-04 04:41pm
8 2701 1-31-04 05:40pm
Result of query should looks like as follows
B 9:00-2:00 2:00-5:00 5:00-10:00
----------------------------------------
2701 4 3 1
I want to group by Unit and by time buckets. Can anyone help me with this? Thanks in advance.
-
--Cross tab query
use tempdb
go
create table mytable (A int, B int, c datetime)
go
insert into mytable select 1, 2701,' 1-31-04 09:40am'
insert into mytable select 2, 2701,' 1-31-04 09:49am'
insert into mytable select 3, 2701,' 1-31-04 10:40am'
insert into mytable select 4, 2701,' 1-31-04 10:04am'
insert into mytable select 5, 2701,' 1-31-04 02:40pm'
insert into mytable select 6, 2701,' 1-31-04 03:35pm'
insert into mytable select 7, 2701,' 1-31-04 04:41pm'
insert into mytable select 8, 2701,'1-31-04 05:40pm'
go
SELECT B,
sum(CASE WHEN (C >convert(datetime,left(convert(varchar(35),C,120), 11) +'09:00:00') and C<=convert(datetime,left(convert(varchar(35),C,120 ),11) +'14:00:00') ) THEN 1 ELSE 0 END) AS [9:00-2:00],
sum(CASE WHEN (C >convert(datetime,left(convert(varchar(35),C,120), 11) +'14:00:00') and C<=convert(datetime,left(convert(varchar(35),C,120 ),11) +'17:00:00') ) THEN 1 ELSE 0 END) AS [2:00-5:00],
sum(CASE WHEN (C >convert(datetime,left(convert(varchar(35),C,120), 11) +'17:00:00') and C<=convert(datetime,left(convert(varchar(35),C,120 ),11) +'20:00:00') ) THEN 1 ELSE 0 END) AS [5:00-10:00]
FROM mytable
GROUP BY B
-
--Result
B,9:00-2:00,2:00-5:00,5:00-10:00
2701,4,3,1
-
Thanks alot. I am going to try it with 100 of diff units and see the result. Thanks again.
-
Whats wrong in following sql statement. Even I have rows between time 10:00pm to 2:00am in the morning it is returning 0 records. If I run it for diff time range like 6:00am to 3:00pm it works fine. Please help me.
Select z.State,d.Affiliate,count(*)
From Call_Center_Data c ,Zip_PZA z, DNIS d
where c.zip_code = z.zip_code
and c.dnis_code = d.dnis
and c.Response_Date between '2-23-04' and '2-28-04'
and CONVERT(char(12), c.Response_Date,8) between '22:00:00' and '02:00:00'
group by z.state,d.Affiliate
Thanks in advance.
-
because your hourly time is defined backward.
You should use
between '22:00:00' and '23:59:59'
or
'00:00:00' and '02:00:00'
-
I want the records between 10:00pm to morning 2:00am so 10:00= 22:00:00 and 2:00am is 2:00:00.
I am confuse with your answer Claire.
-
You can't compare time only in different days, have to specify date and time.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|