Data fetching from table

Question

Hi ,
I have table with list of product columns and its ordered users for specific dates
so I need a sql query to fetch the group the products with specific to each user for the respective given date
For ex:
Product
user
date
Soap
User 1
12-12-2008
Shampoo
User 1
12-12-2008
Tv
User 2
12-12-2008
Stool
User 2
12-12-2008
so I pass the date alone (12/12-2008)-- it should group the products according to user and show
Thanks

Pankajkumar Patel · Accepted Answer

DECLARE @Orders TABLE
(
OrderId int NOT NULL,
UserName varchar(20),
ProductName varchar(20),
OrderDate datetime
)
INSERT INTO @Orders (OrderId, UserName, ProductName, OrderDate) VALUES (1, 'User1', 'Soap', '2008-10-10')
INSERT INTO @Orders (OrderId, UserName, ProductName, OrderDate) VALUES (2, 'User1', 'Shampoo', '2008-10-10')
INSERT INTO @Orders (OrderId, UserName, ProductName, OrderDate) VALUES (3, 'User2', 'Tv', '2008-10-10')
INSERT INTO @Orders (OrderId, UserName, ProductName, OrderDate) VALUES (4, 'User2', 'Stool', '2008-10-10')
INSERT INTO @Orders (OrderId, UserName, ProductName, OrderDate) VALUES (5, 'User1', 'Soap', '2008-12-12')
INSERT INTO @Orders (OrderId, UserName, ProductName, OrderDate) VALUES (6, 'User1', 'Shampoo', '2008-12-12')
INSERT INTO @Orders (OrderId, UserName, ProductName, OrderDate) VALUES (7, 'User2', 'Tv', '2008-12-12')
INSERT INTO @Orders (OrderId, UserName, ProductName, OrderDate) VALUES (8, 'User2', 'Stool', '2008-12-12')
declare @date datetime = '2008-12-12'
; with cte_data as (
select ROW_NUMBER() over(partition by UserName order by UserName, OrderDate ) as SrNo,
UserName, ProductName from @Orders
where OrderDate = @date)
select case when d.SrNo = 1 then d.UserName else '' end UserName, d.ProductName from cte_data as d
Output
UserName
ProductName
User 1
Soap
Shampoo
User 2
Tv
Stool
Hope, this will help you!

Pankajkumar Patel · Answer

Hi Sathish,
You can fetch desired format data from table as following way:
DECLARE @Users TABLE
(
UserId int NOT NULL,
UserName varchar(20)
)
DECLARE @Products TABLE
(
ProductId int NOT NULL,
ProductName varchar(20)
)
DECLARE @Orders TABLE
(
OrderId int NOT NULL,
UserId int NOT NULL,
ProductId int NOT NULL,
OrderDate datetime
)
INSERT INTO @Users (UserId, UserName) VALUES (1, 'User 1')
INSERT INTO @Users (UserId, UserName) VALUES (2, 'User 2')
INSERT INTO @Products (ProductId, ProductName) VALUES (1, 'Soap')
INSERT INTO @Products (ProductId, ProductName) VALUES (2, 'Shampoo')
INSERT INTO @Products (ProductId, ProductName) VALUES (3, 'TV')
INSERT INTO @Products (ProductId, ProductName) VALUES (4, 'Stool')
INSERT INTO @Orders (OrderId, UserId, ProductId, OrderDate) VALUES (1, 1, 1, '2008-12-12')
INSERT INTO @Orders (OrderId, UserId, ProductId, OrderDate) VALUES (2, 1, 2, '2008-12-12')
INSERT INTO @Orders (OrderId, UserId, ProductId, OrderDate) VALUES (3, 2, 3, '2008-12-12')
INSERT INTO @Orders (OrderId, UserId, ProductId, OrderDate) VALUES (4, 2, 4, '2008-12-12')
INSERT INTO @Orders (OrderId, UserId, ProductId, OrderDate) VALUES (5, 1, 1, '2008-10-10')
INSERT INTO @Orders (OrderId, UserId, ProductId, OrderDate) VALUES (6, 1, 2, '2008-10-10')
INSERT INTO @Orders (OrderId, UserId, ProductId, OrderDate) VALUES (7, 2, 3, '2008-10-10')
INSERT INTO @Orders (OrderId, UserId, ProductId, OrderDate) VALUES (8, 2, 4, '2008-10-10')
declare @date datetime = '2008-12-12'
SELECT p.ProductName, u.UserName, CONVERT(varchar, o.OrderDate, 103) as OrderDate
FROM @Orders o
inner join @Products p on p.ProductId = o.ProductId
inner join @Users u on u.UserId = o.UserId
where o.OrderDate = @date
Output :
ProductName
UserName
OrderDate
Soap
User 1
12/12/2008
Shampoo
User 1
12/12/2008
TV
User 2
12/12/2008
Stool
User 2
12/12/2008
Hope, this will help you!

sathish · Answer

Thank you Pankaj Patel

sathish · Answer

Thanks Patel.
But already I have the table like this, I need to fetch records as below, when pass on the date in where condition
User
Product
User1
Soap
Shampoo
User2
Tv
Stool
Thanks

ProductName	UserName	OrderDate
Soap	User 1	12/12/2008
Shampoo	User 1	12/12/2008
TV	User 2	12/12/2008
Stool	User 2	12/12/2008